Re: sysdate, Time Zones and GMT

In article 15955_at_newton.ccs.tuns.ca, James Richard writes:
> In article <803213757snx_at_johnw.supreme.auug.org.au> johnw_at_canb.auug.org.au writes:
> >I need to be able to store date/time information in an Oracle v7 database in
> >GMT (Grenich Mean Time) also known as UTC.
> >
> >The doco says that there is a time conversion function which only supports
> >the timezones applicable for the USA.
> >
> >The application is to be deployed across multiple timezones, yet all data
> >input, output and processing must be done on the same timezone basis, ie GMT.
> >
> >Oracle support says that this is being looked at for v8.
> >
> >Apart from writing a 'C' function call, and invoking it everytime I want to
> >use a date/time, does anyone have any experience/work arounds?
> >
>

V7.1 allows you to write your own functions. You could write one called

Super_New_Time(d, z1, z2) /* Input datetime, from time zone, to time zone */

When/If Oracle supports non-us Time Zones you can then globally change the function name to New_Time from Super_New_Time in your code.

Your function could use a table in the format

TZ_NAME varchar2(3) NOT NULL /* Time zone name, Primary key */ TZ_DELTA number(2) NOT NULL /* Offset from GMT, between -23.5 and +23.5 */

to figure out the difference between the two zones.

Things you should be aware of:

1. Different operating systems have different ways of treating time zone changes. Will this application be ported between operating systems? As far as I know VMS (for example) doesn't have a concept of Time Zones.
2. Working with time as well as date is a nightmare. I've done it and have bitter memories. Try and establish naming conventions which identify which date columns have active time components and which dont. Put a CHECK constraint CHECK (Mydate = Trunc(Mydate)) on any date column which does not allow time to make sure it stays that way.
3. The table I've described does NOT support daylight savings time. It assumes that the offset for each TZ is a constant. If you want daylight savings time you need to add columns eff_datetime and end_datetime and figure out what happens if your function is passed a datetime value which can't be mapped. An example would be getting 01:30 for the day the hour goes forward and jumps from 00:59 to 02:00.

David Rolfe,
SunSoft,
Mountain View,
California. Received on Mon Jun 19 1995 - 00:00:00 CEST