Selecting Abbreviated Timezone

Question

I'm trying to write a query to return the current abbreviated timezone for a given area. Such as...

'America/New_York' 'America/Los_Angeles'

I'd want these to be converted into...

'EDT' 'PDT'

I can do it using Local time zone

select TO_CHAR(CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE),'TZD') from dual;

Which returns 'PDT'. But I need it to return the timezone for any area. Hoping for something like...

select TO_CHAR(CAST(SYSDATE AS TIMESTAMP AT TIME ZONE 'America/Los_Angeles'),'TZD') from dual;

But Oracle doesn't like that Syntax. Any idea how to get it?

Answer 1

What do you consider as "abbreviated timezone"? There is no common standard for timezone abbreviations and many of them are not distinct, see Time zone Abbreviations or these examples:

SELECT tzname, tzabbrev
FROM v$timezone_names
WHERE tzname = 'America/Los_Angeles';

+----------------------------+
|TZNAME             |TZABBREV|
+----------------------------+
|America/Los_Angeles|LMT     |
|America/Los_Angeles|PST     |
|America/Los_Angeles|PDT     |
|America/Los_Angeles|PWT     |
|America/Los_Angeles|PPT     |
+----------------------------+

Or vice versa:

SELECT tzname, tzabbrev, TZ_OFFSET(tzname)
FROM v$timezone_names
WHERE tzabbrev = 'EST'
order by 3;

+----------------------------------------------+
|TZNAME             |TZABBREV|TZ_OFFSET(TZNAME)|
+----------------------------------------------+
|Australia/Yancowinn|EST     |+09:30           |
|Australia/Broken_Hi|EST     |+09:30           |
|Australia/ACT      |EST     |+10:00           |
|Australia/Brisbane |EST     |+10:00           |
|Australia/Canberra |EST     |+10:00           |
|Australia/Queenslan|EST     |+10:00           |
|Australia/Hobart   |EST     |+10:00           |
|Australia/Lindeman |EST     |+10:00           |
|Australia/Victoria |EST     |+10:00           |
|Australia/Melbourne|EST     |+10:00           |
|Australia/NSW      |EST     |+10:00           |
|Australia/Tasmania |EST     |+10:00           |
|Australia/Sydney   |EST     |+10:00           |
|Australia/Currie   |EST     |+10:00           |
|Australia/Lord_Howe|EST     |+10:30           |
|Australia/LHI      |EST     |+10:30           |
|Antarctica/Macquari|EST     |+11:00           |
|America/Moncton    |EST     |-03:00           |
|America/Antigua    |EST     |-04:00           |
|America/Detroit    |EST     |-04:00           |
|America/Fort_Wayne |EST     |-04:00           |
|America/Grand_Turk |EST     |-04:00           |
|America/Indiana/Ind|EST     |-04:00           |
|America/Indiana/Mar|EST     |-04:00           |
|America/Indiana/Pet|EST     |-04:00           |
|America/Indiana/Vev|EST     |-04:00           |
|America/Indiana/Vin|EST     |-04:00           |
|America/Indiana/Win|EST     |-04:00           |
|America/Indianapoli|EST     |-04:00           |
|America/Iqaluit    |EST     |-04:00           |
|America/Kentucky/Lo|EST     |-04:00           |
|America/Kentucky/Mo|EST     |-04:00           |
|America/Louisville |EST     |-04:00           |
|America/Montreal   |EST     |-04:00           |
|America/Nassau     |EST     |-04:00           |
|America/New_York   |EST     |-04:00           |
|America/Nipigon    |EST     |-04:00           |
|America/Pangnirtung|EST     |-04:00           |
|America/Santo_Domin|EST     |-04:00           |
|America/Thunder_Bay|EST     |-04:00           |
|America/Toronto    |EST     |-04:00           |
|Canada/Eastern     |EST     |-04:00           |
|EST5EDT            |EST     |-04:00           |
|US/East-Indiana    |EST     |-04:00           |
|US/Eastern         |EST     |-04:00           |
|US/Michigan        |EST     |-04:00           |
|US/Central         |EST     |-05:00           |
|Jamaica            |EST     |-05:00           |
|America/Cancun     |EST     |-05:00           |
|America/Cayman     |EST     |-05:00           |
|America/Chicago    |EST     |-05:00           |
|America/Coral_Harbo|EST     |-05:00           |
|America/Indiana/Kno|EST     |-05:00           |
|America/Indiana/Tel|EST     |-05:00           |
|America/Jamaica    |EST     |-05:00           |
|America/Knox_IN    |EST     |-05:00           |
|America/Atikokan   |EST     |-05:00           |
|America/Menominee  |EST     |-05:00           |
|America/Merida     |EST     |-05:00           |
|America/Panama     |EST     |-05:00           |
|America/Port-au-Pri|EST     |-05:00           |
|America/Rankin_Inle|EST     |-05:00           |
|America/Resolute   |EST     |-05:00           |
|CST                |EST     |-05:00           |
|EST                |EST     |-05:00           |
|US/Indiana-Starke  |EST     |-05:00           |
|America/Managua    |EST     |-06:00           |
|America/Cambridge_B|EST     |-06:00           |
+----------------------------------------------+

Note, result for TZD depends not only on the region but also on time:

SELECT 
    TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'America/Los_Angeles', 'TZD') AS ZTD_SUMMER, 
    TO_CHAR((SYSTIMESTAMP + NUMTODSINTERVAL(150, 'DAY')) AT TIME ZONE 'America/Los_Angeles', 'TZD') as ZTD_WINTER
FROM dual;

+------------------------------+
|ZTD_SUMMER         |ZTD_WINTER|
+------------------------------+
|PDT                |PST       |
+------------------------------+

Answer 2

AT TIME ZONE is a timestamp modifier and does not belong in the CAST expression.

Hence:

select to_char(cast(sysdate as timestamp) at time zone 'America/Los_Angeles', 'TZD')
from dual;

Or simpler:

select to_char(systimestamp at time zone 'America/Los_Angeles', 'TZD') from dual;

And here is a query to get all current time zones:

select distinct tzname, to_char(systimestamp at time zone tzname, 'TZD') as zone
from v$timezone_names
order by tzname;