Oracle SQL: get timezone for a given date

16,438

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'
Share:
16,438

Related videos on Youtube

Devang Thakkar
Author by

Devang Thakkar

Updated on August 29, 2022

Comments

  • Devang Thakkar
    Devang Thakkar about 1 year

    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?

    • Mr. Mascaro
      Mr. Mascaro about 9 years
      Timezone is based on geography. You would need location data as well as a datetime object.
  • Matt L
    Matt L over 2 years
    Absolute genius. My Oracle server only supports the DATE datatype, and FROM_TZ "optional feature isn't enabled". But wrapping FROM_TZ in TO_CHAR still gives me the offset at the date of each record.