Jirka's Public Notepad

Data Engineering | Python | SQL Server | Teradata

December 6, 2018 By Jiří Hubáček Leave a Comment

Timezone handling in Teradata

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.

-- 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

Related

Filed Under: Teradata Tagged With: don't remember, SQL, Teradata, Teradata SQL

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
© 2021 · Jiří Hubáček, PGP