Re: Time (and time zones) in Oracle 8i

You are correct. The Oracle DATE datatype stores only a date/time value, with no internal representation of the timezone. Oracle also does not provide a function for retrieving the "timezone" setting for a database. And yes, this is an issue for database systems that span multiple timezones.

Oracle does provide functions and operations that allow for adjusting a DATE value forwards or backwards by any number of months, days, hours, minutes, or seconds. But the resulting DATE value still does not carry with it any notion of timezone, or any other adjustments that have been applied. Implementing this type of functionality in Oracle would require additional information be stored (external of a DATE value) or that this information be otherwise inferred or implied.

Here are a couple of workable approaches to addressing this issue within the Oracle database:

• use DATE datatype to store only date/time values of a common standard (known) timezone, one which is not adjusted for daylight savings time.
• store date/time values including timezone using a datatype other than DATE.
• store date/time values using DATE datatype and store related timezone offset in a separate column.
• store some date/time values as "local" timezone and other date/time values as "standard" timezone

Each approach has benefits and drawbacks which need to be evaluated against the overall business and specific application requirements. I will refrain from a dissertation on the reasons for our selection and implementation of the approach we took.

"gnn_gnn" <gnn_gnn_at_hotmail.com> wrote in message news:93amf3$jr$1_at_slb3.atl.mindspring.net...
> Hi:
>
> I am having some trouble getting a clear understanding of time and time
> zones in Oracle (8i). There are functions to transform date-times from
 one
> time zone to another, etc., but if I want to load data with a date-time
> format string like 'YYYY-DD-MM HH24:MI:SS', there is no timezone
 information
> for the template string. It seems like all times entered are assumed to
 be
> in the Oracle server's time zone? What does this mean for enterprises
 with
> servers in different time zones? (Although I don't have this, I am
> curious). This is true for SQLLDR as well. There is no way to tell the
> timezone for the date-times.
>
> With the above in mind, and given that Oracle is used in many global
> enterprise databases around the world, how does one have remote sites do
> something like log events, and have users perform queries that do
 date/time
> boundary searches correctly? For instance, if something happens at 3:00pm
> in California, and something happens at noon in DC the same day, a
 temporal
> query SHOULD find both of those events occured at the same time. But if
 the
> CA log just enters "3:00pm" and the DC log enters "12:00pm", it won't work
> (will it??). Even if Oracle keeps time like unix, seconds since Jan 1
 <pic
> a year>, that IMPLIES a timezone, does it not?
>
> It seems like it is up to the application(s) to all convert to some
 standard
> time zone (Zulu, EST, or whatever) and I would think this is exactly what
> one does NOT want. It means you count on all the distributed sites doing
> the conversion properly, etc. It would seem that each app should enter
> "xx:xx TZ" where TZ is a time zone, and let the server convert it to it's
> time, (or zulu, etc.)
>
> Am I right or wrong? How is this type of situation normally handled?
 Where
> can I find out more? My searches have not come up with anything
 definitive.
>
> Thanks,
> Mike
>
>
>
Received on Sat Jan 20 2001 - 22:07:54 CST