Re: sysdate, Time Zones and GMT

In article cmd_at_shade.twinsun.com, eggert_at_twinsun.com (Paul Eggert) writes:
> drolfe_at_Eng.Sun.COM (David Rolfe) writes:
>
> >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 */
>
> Bad idea. There's no standard for time zone names;
> `EST' means something quite different in the US than it does in Australia.

AhHa! That explains why Oracle hasn't made their NEW_TIME function work outside the US. You could always add another parameter which says which part of the world you are talking about.  

> If you care about time zones, store UTC (GMT) in the database,
> and convert to and from local time in the application.
> Anything else leads to madness.

Been there. Went mad. Have the T-shirt to prove it.

Our problem was that we were in California and the machine ( a VAX) was always on GMT. Because a VAX has no concept of time zones and CA has daylight saving time we had to come up with a table and procedure to figure out whether we were in PST or PDT at any given moment in time, so we could convert it correctly. Various weird anomlies came up in this environment:

If you subtract two dates (both midnight local time) you will not get an integer back if one is in Summer and the other in Winter.

Life gets very strange indeed for the two hours of the year when daylight savings begins and ends.

You can't use the TRUNC function on dates easily

EVERY date column in a Form will have to be hidden and have a display version which shows what it is in local time. It will also need a trigger that converts the entered date to GMT and stores it in the hidden field.

This wan't my idea. I was the poor fool who had to make it work. I heartily agree with using GMT, provided your hardware supports time zones. One should also have a REAL business need before getting into this. This was not the case in the system described above. It's going to add 10-15% to the build cost of a project. Most businesses want to know what day something happens, not the exact time to the second as measured from Buckingham Palace.

In my experience any discussion of Time Zones always leads towards confusion. I intend to bow out of this discussion now before we all confuse the hell out of each other...

David Rolfe,
SunSoft. Received on Wed Jun 28 1995 - 00:00:00 CEST