I want to understand how Oracle does daylight savings time
Is the DST just a DATE formatting option, or are there other ways to
get Oracle to consider daylight savings time in his internal
Are there parameters that affect the DST date settings?
My guess is that there is a date format that displays DST . ie:
?YYYYY-MM-DD HH:MM:SS DST?
Am I correct that daylight savings time is just a DATE format mask?
Answer: First, Oracle requires
a TIMEZONE format, as opposed to a DATE format, an important
Oracle never actually changes a TIMEZONE column value
when you set your system to be on daylight savings time. There
built-in DST DATE conversion functions for changing to daylight
If jobs and windows specifications use the calendaring syntax but
omit the start date, the Scheduler derives the time zone from the
Oracle recommends that you set the
attribute to a region's name instead of absolute time zone offset,
in order to ensure that daylight saving adjustments are being taken
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
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
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 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
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
Get the Complete
Oracle SQL Tuning Information
The landmark book
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
for 30% off directly from the publisher.
Burleson is the American Team
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
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
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
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.