All Forums UDA
Umesh Bansal 6 posts Joined 01/08
15 Feb 2008
How we can use timestamp with timezone in teradata(Eqv to from_tz in oracle)

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.00I want to achieve same in TD. I have tried following in TDselect 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:00But i want same like in oracle.Any suggestion will welcome.Umesh

kabita 1 post Joined 02/08
15 Feb 2008

Hi,Even i also have almost same requirement.Please let me know the solution to this.Thanks, Kabita

dnoeth 4628 posts Joined 11/04
22 Feb 2008

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

Dieter

DannyChin 8 posts Joined 07/09
26 Oct 2011

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 3 posts Joined 05/09
20 Dec 2011

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 2 posts Joined 10/09
05 Apr 2012

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 101 posts Joined 10/11
02 Dec 2014

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

dnoeth 4628 posts Joined 11/04
02 Dec 2014

This should work:

timestampcol AT 'America Pacific'

 

Dieter

Harpreet Singh 101 posts Joined 10/11
05 Dec 2014

Thanks Dieter. 

You must sign in to leave a comment.