I needed to display local timestamps of UTC stored data in my report. But I didn’t want to just blindly add or subtract hours as this wouldn’t reflect daylight saving time shifts.
Unfortunately, there’re not so many internet resources so I had to opt for Teradata’s documentation. I found a simple AT expression where one specifies time zone name and Teradata handles the shifting and daylight saving. Simple.
1 2 3 4 5 6 7 8 9 10 11 | -- returns: 2018-01-01 11:00:00.000000+11:00 SELECT CAST('2018-01-01 00:00:00' AS TIMESTAMP WITH TIME ZONE ) AT 'Australia Eastern' -- returns: 2018-08-01 10:00:00.000000+10:00 SELECT CAST('2018-08-01 00:00:00' AS TIMESTAMP WITH TIME ZONE ) AT 'Australia Eastern' -- returns: 2018-01-01 02:00:00.000000+01:00 SELECT CAST('2018-01-01 00:00:00' AS TIMESTAMP WITH TIME ZONE ) AT 'Europe Central' -- returns: 2018-08-01 02:00:00.000000+02:00 SELECT CAST('2018-08-01 00:00:00' AS TIMESTAMP WITH TIME ZONE ) AT 'Europe Central' |
And available time zone list:
| Africa Egypt Africa Morocco Africa Namibia America Alaska America Aleutian America Argentina America Atlantic America Brazil America Central America Chile America Cuba America Eastern America Mountain America Newfoundland America Pacific America Paraguay America Uruguay | Asia Gaza Asia Iran Asia Iraq Asia Irkutsk Asia Israel Asia Jordan Asia Kamchatka Asia Krasnoyarsk Asia Lebanon Asia Magadan Asia Omsk Asia Syria Asia Vladivostok Asia West Bank Asia Yakutsk Asia Yekaterinburg | Australia Central Australia Eastern Australia Western Europe Central Europe Eastern Europe Kaliningrad Europe Moscow Europe Samara Europe Western Indian Mauritius Mexico Central Mexico Northwest Mexico Pacific Pacific New Zealand Pacific Samoa |
source: https://docs.teradata.com/reader/kmuOwjp1zEYg98JsB8fu_A/aWY6mGNJ5CYJlSDrvgDQag
Leave a Reply