Daylight Saving Time and Sybase Server Products
Most Sybase Server Products do not have any direct support for handling daylight saving time. This document examines the issues and suggests ways to avoid problems. Sybase recommends, as a best practice, running Sybase Servers under the Coordinated Universal Time (UTC) standard and having all conversions to local time zones (including daylight saving time adjustments) performed by the client applications.
Additionally, applications written in Java that employ unpatched versions of the Java Developer's Kit/Java Runtime Environment may be at risk of incorrectly reporting the local-time offset from UTC.
How Sybase Products Use Time and Date
Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added to some servers to provide the current datetime value in Coordinated Universal Time regardless of the time zone the server is otherwise running under.
The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time. Sybase Servers only recognize and store the date and time portions of the values provided by the operating system, which are based on the time zone configured at the operating system level (typically though the TZ environment variable setting in Unix or the Date/Time function of the Windows Control Panel) for the user who started the product. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4th year, except for every 100th year, except for every 400th year), but do not include any adjustments for leap seconds or transitions from daylight saving time to regular time.
Most Sybase Servers usually have two sources for datetime values. The getdate() and getutcdate() functions always make a call to the operating system to get the current time with the greatest accuracy. Most Servers also maintain an internal clock, which it uses to avoid the overhead of making a system call in cases where strict accuracy is less important. For example, Sybase ASE relies on its own clock for the password change date field pwdate in syslogins, creation and modification date fields in system catalogs, and begin and commit transaction times (which are visible in the syslogshold table, and used for the with until_time option of load transaction).
The internal clock is initialized at start time with the current value of the operating system clock and incremented based on regular SIGALRM signals from the operating system (typically 10 per second). Once a minute, Sybase ASE polls the operating system clock to get the current time. The two clocks sometimes fall out of synchronization. When this happens, Sybase ASE speeds up or slows down the internal clock to minimize the difference with the operating system clock.
The Effect of Daylight Saving Time
Most UNIX systems actually run on UTC; there are no daylight saving adjustments in the UTC definition. Such adjustments are taken care of in applications, normally by calling OS library functions.
If in effect, daylight saving time causes a large discontinuous jump in the time value received from the OS, typically either forward by an hour or backwards by an hour. The getdate() function, which gets its information directly from the operating system, immediately picks up this change. However, the server cannot immediately synchronize its internal clock.
System-generated datetime values, such as crdates in sysobjects, pwdate in syslogins, and begin tran and commit tran times in log records and the syslogshold table will not match the new operating system clock, though the difference will decrease over time. Use of load tran with until_time is also effected by this. Until the internal clock has synchronized with the operating system clock, the "until time" is not accurate.
The various common date and time functions are also unaware of daylight saving time. For example, if you use the datediff() function with values that cross one or more daylight saving time boundaries, the results are not adjusted for this change.
Recent Changes to Daylight Saving Time in the USA
In the USA, a law named the Energy Policy Act was passed which altered the starting and ending dates of Daylight Saving Time by 4 weeks starting in March of 2007. Sybase Servers do not contain any built-in knowledge of daylight saving time, the adjustments are made based on OS libraries and function calls. Presuming the Sybase Product is running under daylight saving time, these products will reflect these changes if the OS has been updated.
Time Conversions Done in the Java JDK/JRE
The Java Run-time Environment (JRE) contains library functions for time conversion from Coordinated Universal Time (also refered to as GMT) to local time, including any Daylight Saving Time compensation. These library functions were developed prior to the United States Energy Policy Act of 2006 and may not include the changes to Daylight Saving Time in US Time Zones that result from that act. Patches to the JDK/JRE will be issued as necessary to ensure these functions are updated by both Sun and Sybase Inc.
The following advice is provided by Sun Microsystems:
The Java Runtime Environment (JRE) stores rules about DST observance all around the globe. Older JREs will have outdated rules that will be superseded by the Energy Policy Act of 2005. As a result, applications running on an older JRE may report incorrect time from March 11, 2007 through April 2, 2007 and from October 29, 2007 through November 4, 2007.
Solutions for Java Applications:
If you are concerned about application failures that may result from these DST changes, you should update your Java Runtime Environment. The following Java platform versions have correct time rules to handle the DST changes that will affect U.S. time zones in 2007. You can download any of the following Java platform versions to resolve this DST issue:
Testing Daylight Saving Time Changes
Testing Daylight Saving Time changes is non-trivial. Simply changing the OS clock forward or back an hour is a poor test as it is changing the root OS time, and the time zone adjustments (made through calls to the OS libraries) are being bypassed. Testing probably should be done on dedicated machines where the system clock can be changed to values just before the transition time into or out of daylight saving time, the application (i.e. Sybase Product) started and allowed to run as the OS clock advances through the transition.
To avoid such issues, the best practice is to run Sybase products so that they are not subject to daylight saving time, i.e. run them on a constant clock, such as Greenwich Mean Time (GMT) or Coordinated Universal Time (UTC). You can use one of these standards for all datetime values in the server and let clients be responsible for conversions to their local time zone, including adjustments for daylight saving time. The dateadd() and datediff() calculations will then be correct even if the values in question span the change in or out of daylight saving time.
The choice of which timezone to run the server under is unfortunately often based on where the company's headquarters are at the time the server is first created, which usually works well for small companies that don't have operations in other time zones. However, headquarters are sometimes moved, and small companies can grow to become global companies. Establishing a company standard early on that the server runs under UTC and all clients are responsible to translating from UTC to local time can save a great deal of trouble in the future.
What Sybase Customers should do
Customers should contact operating system supplier for the updates for their particular operating system. Some Sybase products bundle the JDK/JRE. Customers using these products should get the updated JDK/JRE from the supplier or get the Sybase product update that bundles the fixed JDK/JRE.
Click here for a complete list of Sybase products and instructions - Last Updated 6th March 2007.
If you cannot avoid making daylight saving time adjustments, the best practice is to shut down the product before the operating system clock is reset; you can restart immediately after the clock is reset. This is precautionary; changing the clock while the product is running usually does not cause problems. However, unpredictable effects can occurred, for example, WAITFOR commands or dumps hanging, or SIGALRMs not being received by the product. If a precautionary shutting down of the product while the clock is changed is not possible and such problems are subsequently seen, the product can be restarted at any convenient time to reinitialize the internal clock.
To assist you, Sybase Engineers have prepared the attached matrix of products and indicated their dependency on Operating System time information. Where applicable, known operating system patches have been recommended.
Additional Recommended Reading:
- Calendrical Calculations by Dershowitz and Reingold, Cambridge University Press. ISBN 0-521-56474-3
- Developing Time-Oriented Database Applications in SQL by Snodgrass, Morgan Kaufmann Publishers. ISBN 1-55860-436-7
- Sun Developer Article: "U.S. Daylight Saving Time Changes in 2007" by O'Conner: http://java.sun.com/developer/technicalArticles/Intl/USDST/
- Unix MAN pages on "TIMEZONE(4)"
Copyright © 2006 Sybase, Inc. All rights reserved.