How we can use timestamp with timezone in teradata(Eqv to from_tz in oracle)

[Umesh_Bansal]

Hi,

FROM_TZ function in Oracle converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

In oracle

select from_tz(TIMESTAMP '2008-02-15 01:50:42', '-7:00')
from dual;

will return 15-FEB-08 01:50:42.000000000 AM GMT -07.00

I want to achieve same in TD.

I have tried following in TD

select cast('2005-09-11 13:20:53'||'+03:00' as timestamp with time zone format 'YYYY-MM-DDBHH:MI:SSDS(F)Z')

2005-09-11 13:20:53.000000+03:00

But i want same like in oracle.

Any suggestion will welcome.

Umesh

[kabita]

Hi,
Even i also have almost same requirement.

Please let me know the solution to this.

Thanks,
Kabita

[dnoeth]

What do you mean by "same like in oracle"?

Exactly the same format? There's only UTC +/- time zone in Teradata, no GMT or EST.

Or something different? It looks like the same answer for me.

Dieter

[DannyChin]

Hi. I also have the same requirement of converting a timestamp from one timezone to another. The example below is from Oracle. Is there such function in Teradata?

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',

     'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')

     AT TIME ZONE 'America/Los_Angeles' "West Coast Time"

FROM DUAL;

West Coast Time

----------------------------------------------------------

01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

Thanks,

Danny

[jim]

All you need to do is add or subtract the "interval" from the time. So for example, to go from a current time on the East Coast to the West Coast you simply subtract an interval of 3 hours.

Select current_timestamp - interval '03:00' hour to minute;

Going from West to East, add the 3 hours.

Select current_timestamp + interval '03:00' hour to minute;

If you wanted it to figure out the hour difference between 2 places represented by text fields, then you would have to create a table with the places and time differences, then use that in the calculation or perhaps create a stored procedure to do it..

[Attila]

Hi Jim, et al.  We have a similar need, but want to factor in Daylight Saving Time. 

Our global data has three timezones: CDT for Americas, GMT for Europe/ME/Africa, and China Standard Time for Asia/Pacific.  The first two use DST but switch on different dates in the year, and the third doesn't use DST. 

Therefore we can't hardcode simple interval arithmetic.  I believe Oracle's FROM_TZ handles DST automatically.  Is there such an option in Teradata? 

[Harpreet_Singh]

Hi All,

Is there function or standard code to convert UTC/GMT into PST with daylight saving.

[dnoeth]

This should work:

timestampcol AT 'America Pacific'

[Harpreet_Singh]

Thanks Dieter. 

[teradatauser2]

Hi Dieter,

I have a followup question on your earlier post, so am posting in this. I have to convert a timestamp from GMT to EST. I can use 'at   ameriacan eastern', as you mentioned.I wanted to chek if this takes into account the day light saving as well. I am pretty sure about this as it mentions in the manuals, but wanted to confirm my understanding.

Note: Teradata Database will automatically adjust the time zone displacement to account for
the start or end of daylight saving time only if you specify a time zone using a time zone string
that follows different DST and standard time zone displacements.

--Samir Singh