Oracle SQL: get timezone for a given date

Question

How can the timezone be determined accurately for a given date (DATE data_type column) based on the following information?

a) DATE columns do not store timezone information.

b) Server timezone is either EST or EDT depending on whether date light saving is in effect or not

c) The current timezone offset can be easily fetched by SQL:

SELECT TO_CHAR(SYSTIMESTAMP,'TZH:TZM') FROM DUAL

Assuming that timezone for today (3-Nov-2014) is EST, how can we determine what was the timezone in effect (i.e. EST or EDT) for a given date (e.g. 21-Oct-2014) programatically?

Answer 1

Oracle has support for IANA time zones. The US Eastern time zones is represented by "America/New_York".

You can use FROM_TZ to create a TIMESTAMP WITH TIME ZONE from a TIMESTAMP value. (If you're starting from DATE then you should first cast to TIMESTAMP.)

Then you can use TO_CHAR with either 'TZH:TZM' to get an offset, or with 'TZD' to get an abbreviation (such as EST or EDT).

For example:

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-02 00:00:00', 'America/New_York'),'TZD')
FROM DUAL;  -- Output: 'EDT'

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-02 00:00:00', 'America/New_York'),'TZH:TZM')
FROM DUAL;  -- Output: '-04:00'

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-03 00:00:00', 'America/New_York'),'TZD')
FROM DUAL;  -- Output: 'EST'

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-03 00:00:00', 'America/New_York'),'TZH:TZM')
FROM DUAL;  -- Output: '-05:00'