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

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;