Time Zone String Instead of Offset

Database
Enthusiast

Time Zone String Instead of Offset

When querying a TIMESTAMP WITH TIME ZONE column, is it possible to return the TIMESTAMP WITH TIME ZONE value using the time zone string (e.g. Americ Eastern) instead of the numeric offset (e.g. -04:00)? For example, I would like to return '9/26/2014 09:00:00.000 America Eastern' instead of '9/26/2014 09:00:00.000-04:00'.

Thanks in advance!

5 REPLIES
Enthusiast

Re: Time Zone String Instead of Offset

Which version of TD?

Try this:

select current_timestamp at 'gmt' (title 'GMT TIME'),
current_timestamp at 'america pacific' (title 'America Pacific TIME'),
current_timestamp at 'america eastern' (title 'America Eastern TIME');

.......

Enthusiast

Re: Time Zone String Instead of Offset

Version 14.10.

I received:

GMT TIME                                                America Pacific TIME                           America Eastern TIME

1 11/17/2014 20:39:30.890000+00:00 11/17/2014 12:39:30.890000-08:00 11/17/2014 15:39:30.890000-05:00

Enthusiast

Re: Time Zone String Instead of Offset

I dont fell there is timestamp format available to convert zone into string. You can have timezones converted to string using case statement. FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z'

Teradata Employee

Re: Time Zone String Instead of Offset

The problem is that one time zone offset can be valid for multiple time zone names, so there is no universal way to do this. Even if you are dealing strictly with US time zones, not all locales observe daylight saving (plus for those that do, the start / end rules have changed over time).

Enthusiast

Re: Time Zone String Instead of Offset

Thank you!