Re: Oracle dates and timezones

quan_at_hpcc01.corp.hp.com (Suu Quan) wrote:
>
> Seems like the Time Zone should be an intrinsic part of the DATE. Since
>Oracle does not do it, we -as user- will have to enforce it.
>

How would the system know the time zone? Who would tell it? How many timezones are there in the world? What if Alaska time becomes PST? Or we dump daylight savings time?

>What I mean is :
>every date column should be accompanied by a TZ column qualifier.
> xaction_time date,
> xaction_tz varchar(12),
> birth_date date,
> birth_tz varchar(12),
> xxx_time date,
> xxx_tz varchar(12)
>
> Comments ?

Most organizations which are international, like the weather bureaus, the military, aviation, track zulu or GMT time.

So, you could:

Save time as Greenwich Mean Time at all sites.

Then, use the Pl/SQL date function new_time to convert it, if you can track which timezone you saved the time in.

OR-

Better, yet:

Save in GMT.

Then, Have a global-enterprise-wide time table:

      
      time-zone-id            time-id-type     
      start-effective-date    date - GMT           
      end-effective-date      date - GMT
      delta-from-gmt          delta-type
     

  The effective-date columns are for variations in local time, like daylight savings time, or if your timezone changes, from a move or a government edict. The end-effective-date can be set to the Oracle maximum in 4,xxx AD if your time does not change. Else, add a row for each time change, ie daylight savings. Don't enter the change until right before it happens.

Create a date-type for the actual date
and enforce its use. Slap all rogue DATE times down.

Then,wherever you save a date, save it as GMT, providing a stored procedure to get the local time given GMT and the time-id, and a stored procedure to save it as GMT given the time-id and local time.

Of course, you'll have to save both the time and time id in your table, but that's a small price to pay for having clean times. Don't make a type to hold both date and time id. Keep the date pure.

Also, when done, try setting your system time back & ahead 1 year, or even one hour, and see what happens!!

-Austin Received on Fri Mar 29 1996 - 00:00:00 CET