Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Daylight savings time, shutting down oracle

Re: Daylight savings time, shutting down oracle

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: Sun, 29 Oct 2000 06:43:58 GMT
Message-ID: <87hf5w401z.fsf@HSE-MTL-ppp62507.qc.sympatico.ca>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

> >> Is there any point to this? Can anyone describe how oracle handles
> >> this or point me to a resource so I can bring the information to them
> >> to show that there is no reason for this? It seems to me, the box
> >> really runs GMT behind the scenes, where there is no such thing as
> >> daylight savings time.

Just to clear up one key semantic difference, Unix does not "run in GMT behind the scenes". It runs in "seconds since an arbitrary point in time" which is completely independent of timezone. In other words, the same number of seconds have passed since that arbitrary point in time regardless of where the sun is in your sky.

Timezones in most well-designed systems are purely a display formating convention. Time is calculated in an unambiguous absolute form internally and the timezone is only needed for printed representations. This removes the necessity for carrying around the timezone for each timestamp and doing conversions from one timezone to another.

Unfortunately Oracle's date handling is very very weak. Oracle uses the local time which does of course depend on the current timezone and daylight savings status. This means for example that records with a time stamp inserted using sysdate will go backwards an hour. If your data representation doesn't handle this then you may have a problem.

For an example we have graphs that show records inserted per minute, using a column that is set by default to sysdate. We're going to have a huge spike for 2am-3am today because the records for both hours will both be counted. Also other parts of the application checks the records for the past n minutes, which will actually check more records now than it's supposed to. Nothing in our database should break too badly though. It's entirely possible however to have an application that assumes for example that a record previously inserted should have a timestamp strictly in the past.

It's rather incredible that Oracle's time handling is so weak. This isn't a new problem, Unix has always used unambiguous time formats since the 70s, Even the MacOS got this right in '84, and Windows applications eventually figured it out. It's remarkable that a system designed for 24x7 operation could get it wrong in this day and age.

The work-around would be to set Oracle to GMT and handle all date display issues in the application. This would also avoid most y2k bugs since Oracle's default representation is also y2k-bug-prone as well. Date handling is definitely not an area for Oracle to be proud of.

-- 
greg
Received on Sun Oct 29 2000 - 01:43:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US