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
Hi,Even i also have almost same requirement.Please let me know the solution to this.Thanks, Kabita
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
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"
West Coast Time
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
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..
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?