Oracle dates and timezones
Date: 1996/03/20
Message-ID: <31502810.73DC_at_mailhost.fsic.ford.com>#1/1
[I asked a similar question before and got no response so I'll try again]
How does one deal with timezones and DST (Daylight Savings Time) in Oracle dates?
From everything I can see, Oracle stores dates "as entered" with no tracking of the timezone or DST status of the date. On a worldwide application, this can be problematic as (for example) someone in Dearborn, Michigan looking at a record entered in Warley, England will see a date that is offset from Dearborn time by 4 or 5 hours (depending on DST).
For a "live" query, this is not too hard as I could somehow figure out what the timezone and DST status is at the "viewer" and use NEW_TIME() to do the shift. But what about during a report? How do I know what timezone conversion to use to specify a date range for a query?
One thing that helps is to write applications such that all dates are stored in the database in
UTC so I always see a date in the database in a known timezone regardless of the point of entry,
but I've still got the problem of running reports which select date ranges and state dates using
the timezone of the report initiator. This is especially true if I want to give users a
reporting tool like IQ or Business Objects or Crystal Reports or MS-Access or .....
Any suggestions? (Ideally someone would offer a PL/SQL stored procedure equivalent to the Unix
library routines localtime(), gmtime() and mktime() but that might be dreaming)
--
Bill Meahan wmeahan_at_ford.com
Ford Motor Company -- End User Support - North America
Not an official statement of Ford Motor Company or anyone else
except the author.
Received on Wed Mar 20 1996 - 00:00:00 CET