Oracle Daylight Savings Time (DST) Date Conversion Tips

• current_date

• current_timestamp

• localtimestamp

• dbtimezone

• sessiontimezone

• extract

• from_tz

• to_timestamp

• to_timestamp_tz

• to_yminterval tz_offset

If jobs and windows specifications use the calendaring syntax but omit the start date, the Scheduler derives the time zone from the default_timezone attribute.

Oracle recommends that you set the  default_timezone attribute to a region's name instead of absolute time zone offset, in order to ensure that daylight saving adjustments are being taken into account.

Periodically, Oracle updates the time zone files supplied with the database.  These updates reflect any changes in transition rules for affected time zones.  For Oracle 11g, the time zone files are updated to version 3, which includes changes in the Daylight Savings Time rules implemented in the United States that took effect in 2015.

When the transition rules for Daylight Savings Time (DST) change, existing data, particularly that with the datatype of TIMESTAMP WITH TIME ZONE, may be affected.  The way Oracle stored this data makes it susceptible.  Data entered with TIMESTAMP WITH TIME ZONE is converted to Coordinated Universal Time (UTC), formerly referred to as Greenwich Mean Time (GMT).  The conversion is conducted in accordance to whatever transition rules exist in the time zone file.  The information on the original time zone is stored along with the specific time data.

Conversely, when Oracle retrieves data, it must be converted from UTC back to its original entered format.  How this is accomplished is also dependent on the version of the transistion rules in place at the time of the conversion.  For example, data stored when version 2 of the transistion rules were in effect could be affected if retrieved with version 3 rules in place.

The Oracle documentation references a particularly applicable case resulting from the changes in the start/end dates of Daylight Savings Time in the United States.  In 2015, DST rules changed such that DST is extended through the first Sunday in November from the former date corresponding with the last Sunday in October.  In 2015, the last Sunday in October was October 28 and the first Sunday in November was the 4th.

Let's take a look at what happened to data stored between those two dates when the time zone transition rules changed from version 2 to version 3.

With the version 2 rules in effect, Halloween of 2015 in California with the value of TIMESTAMP '2007-10-31 12:00:00 America/Los_Angeles' would have been stored as UTC value '2007-10-31 20:00:00' plus the time zone ID for 'America/Los_Angeles' which carried with it a value of (UTC-08) to correspond with Pacific Standard Time because the time zone transition rules in effect told Oracle the time change happened on October 28th.

When version 3 of the time zone transition rules came along, they took into account the new starting/ending dates for DST.  Now, when Oracle looks back at the dates between October 28 and November 4, 2015, the UTC offset for that time is (UTC-07), correctly corresponding to the fact that DST was still in effect at the time the data was collected.

Retrieving our Halloween data, stored under version 2 transition rules, using an updated version of the database equipped with the version 3 rules results in the following: TIMESTAMP '2007-10-31 13:00:00 America/Los_Angeles'. Note the one hour difference in the returned date!!

The solution to the problem is dependent on the event the data is intended to represent.  Is this piece of data intended to represent noon on Halloween day in Los Angeles?  Or, is it intended to represent the local time in Los Angeles relative to 8pm in UTC.

In the first case, the one hour difference was introduced by the updated DST transistion rules and the timestamp must be updated for any data recorded between the expected and actual time change dates, which would be between October 28 and November 4, 2015 in this example.

In the second case, the value is corrected automatically when the time zone transition file was updated.  There is no further action to be taken.  In fact, updating the timestamp values in this case would actually cause the data to be wrong, so it's important to understand the point of reference for what the data represents.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

---

---

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.