Daylight Savings Time and ASE
ASE does not have any direct support for handling daylight savings time. This document examines the issues and suggests ways to avoid problems. Sybase recommends as a best practice running ASE under the Universal Coordinated Time standard and having all conversions to local time zones (including daylight savings time adjustments) performed by the client applications.
How ASE Uses Time and Date
Adaptive Server Enterprise supports the use of date and time data through the datetime and smalldatetime datatypes (and, starting in 12.5.1, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added in ASE 12.5.3 to provide the current datetime value in Universal Coordinated Time regardless of the time zone ASE is otherwise running under.
The datetime and smalldatetime datatypes, however, do not store time zone information and ASE is entirely ignorant of the concepts of time zones and daylight savings time. ASE only recognizes and stores 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 ASE. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4 th year, except for every 100 th year, except for every 400 th year), but do not include any adjustments for leap seconds or transitions from daylight savings time to regular time.
ASE has 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. ASE also maintains 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, 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, ASE polls the operating system clock to get the current time. The two clocks sometimes fall out of synchronization. When this happens, ASE speeds up or slows down the internal clock to minimize the difference with the operating system clock.
The Effect of Daylight Saving Time
UNIX systems actually run on UTC; there are no daylight savings adjustments in the UTC definition. Such adjustments are taken care of in applications, normally be calling OS library functions.
If in effect, daylight savings 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. ASE notices, during the once-a-minute synchronization check, that the internal clock no longer agrees with the system clock and speeds up or slows down the internal clock. However, the adjustment takes time to complete; no matter how much the internal clock is slowed, it cannot run backwards to close the gap.
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 should decrease over time. Use of load tran with until_time is also effected by this - until the internal clock has synched with the operating system clock, the "until time" is not accurate.
The various ASE date and time functions are also unaware of daylight savings time. For example, if you use the datediff() function with values that cross one or more daylight savings time boundaries, the results are not adjusted for this change.
Recent Changes to Daylight Savings Time
In the USA, a law named the Energy Policy Act was passed which altered the starting and ending dates of Daylight Savings Time by 4 weeks staring in March of 2007. ASE does not contain any built-in knowledge of daylight savings time, the adjustments are made based on OS libraries and function calls. Presuming ASE is running under daylight savings time, ASE will reflect these changes if the OS has been updated.
Testing Daylight Savings Time Changes
Testing Daylight Savings Time changes is tricky. 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 savings time, the application (i.e. ASE) started and allowed to run as the clock advances through the transition.
To avoid such issues, the best practice is to simply to run ASE so that it is not subject to daylight savings time. Instead, run it 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 savings time. The dateadd() and datediff() calculations will be more correct if the values in question are consistently part of the same standard.
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 UTC and all clients are responsible to translating UTC to local time can save a great deal of trouble down the road.
If you cannot avoid making daylight savings time adjustments, your best practice is to shut down ASE before the operating system clock is reset; you can restart immediately after the clock is reset. This is precautionary; changing the clock while ASE is running usually does not cause problems. However, unpredictable effects have occurred, for example, WAITFOR commands or dumps hanging, or SIGALRMs not being received by ASE. If a precautionary shutting down of ASE while the clock is changed is not possible and such problems are seen subsequently, ASE can be restarted at any convenient time to reinitialize the internal clock.
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
Unix MAN pages on “TIMEZONE(4)”