Oracle SQL: get timezone for a given date
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'
Related videos on Youtube
Devang Thakkar
Updated on August 29, 2022Comments
-
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 about 9 yearsTimezone is based on geography. You would need location data as well as a datetime object.
-
-
Matt L over 2 yearsAbsolute 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.