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?
Is there function or standard code to convert UTC/GMT into PST with daylight saving.
This should work:
timestampcol AT 'America Pacific'
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.