Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Daylight Savings Time (DST) Date Conversion Tips

Expert Oracle Database Tips by Donald BurlesonMarch 25, 2015

 

Question:  I want to understand how Oracle does daylight savings time (DST). 

Is the DST just a DATE formatting option, or are there other ways to get Oracle to consider daylight savings time in his internal SYSDATE? 

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 distinction!

Oracle never actually changes a TIMEZONE column value when you set your system to be on daylight savings time.  There are several built-in DST DATE conversion functions for changing to daylight savings time:

  • 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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 




























































































































































   

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster