Equivalent to Oracle's New_Time()


Equivalent to Oracle's New_Time()

Hi Friends,

Can we get your kind co-operation sharing knowledge on Equivalent to Oracle's New_Time() function which we require to implement in our migration project in TD.

Oracle Function: New_Time('Timestamp',In_Zone,Out_Zone);

I appreciate your effort and time finding such valuable solutions & looking forward to get an early response in this regard soon.

Thanks... in advance,

bbhusan... a TD lover
Teradata Employee

Re: Equivalent to Oracle's New_Time()

I am not an expert in Oracle function. I assume you are looking for conversion from one Time Zone to another.

Teradata support "AT TIME ZONE"; see "AT TIME ZONE" in "SQL Reference: Functions and Operators" manual. I think it will help you design an expression.

For example:

select current_time, (current_time AT TIME ZONE interval -'08:00' hour to minute);

Re: Equivalent to Oracle's New_Time()

I struggled with the same thing, and New_Time only works for US time zones. The following query returns all time zone adjustments, in my case relative to Central Time:

select t.*,
tz_offset(tzname) offset,
sysdate local_time,
sysdate +
((to_number(substr(tz_offset(t.tzname), 1, 3)) / 24) -
(to_number(substr(tz_offset('US/Central'), 1, 3)) / 24) +
(to_number(substr(tz_offset(t.tzname), 1, 1) ||
substr(tz_offset(t.tzname), 5, 2)) / 1440) -
(to_number(substr(tz_offset('US/Central'), 1, 1) ||
substr(tz_offset('US/Central'), 5, 2)) / 1440)) as tz_time
order by offset, tzname