Re: DATE field and daylight saving time

Thanks for the suggestions, everyone.

No perfect answers yet ;-)

I have two complications which rule out nearly all your answers I'm afraid, folks! Sorry!

Complication Number 1: The dates stored in our DATE fields can be from a variety of time zones. I had to rule out using a single time-zone/DST independent non-DATE format (such as time_t or UTC/GMT) because the database structure is "published" to our customers who then write their own reports. So the dates have to be meaningful to them.

For example, we have a "jobs" table with a start and end date/time. Jobs can last anything from a few minutes up to a whole day. Imagine the confusion that can arise with jobs scheduled around that "grey area" where DST changes!  This is a more realistic problem than the rather theoretical one I quoted to start with (I was just trying to keep things simple!).

(Incidentally, before you ask, our applications know what time zone a job's date/times refer to because of the job location - we have a separate table with post/zip codes matched to time zones).

Complication Number 2: We're not just talking about BST/GMT here. We're starting to supply our applications to customers in the U.S where our services can span 3 or more time zones. In fact, because of this multiple time zone handling requirement, we're getting rid of practically all SYSDATE usage and using dates generated in our applications (C calls to localtime()).  This gives us much more flexibility over time zones because we can set the TZ environment variable on the fly. Oracle's own time zone handling is pretty limited - it seems to be fixed by the value of TZ on the server before you start up Oracle.

Tommy Wareing (see below) emailed me directly with the idea of storing in GMT but using DB triggers to adjust by time zones for a view onto the data.

This is a neat idea except... Our apps are intended to be international and I'd have to hard-code the time zone rules in PL/SQL - I've looked into this and believe me it is *NON-TRIVIAL*! There are all sorts of huge complications with time zones and DST - Unix sorts it all out internally so I don't have to worry about it in my apps (and DST rules change occassionally in different locales as well!). I really, really don't want to do this <vbg>!

CONCLUSION:
Any solution which is (a) dependent on all DATEs being in a single, sequential-date format such as time_t or UTC is a non-starter because we must store dates relative to appropriate time zones and (b) any solution which requires me to hard-code time zone rules for GMT/BST or any other time zone is too difficult and not future-proof. The only solution so far is Alan Mills'
> Given that you 'unique' value is proving to be less unique than you had
> originally hoped I suspect that you will probably have no option but to
> change your table design.

I think he's right, but I'd welcome any more suggestions..?!

FYI, here is the suggestion Tommy Wareing posted direct to me - it might be useful to anyone with a slightly less complicated problem than mine...

> Create a package variable called... TZ!
> initialise it according to the user's timezone. Um. Don't know how
> you get that to be honest. Look up their username/remote address
> in a table?
>
> Put DB triggers to fire on all fields so that the when the data is
> written to the database it is adjusted by the timezone (thus all the
> data is always stored in GMT). Then when you extract the data,
> you must adjust it (in the opposite direction) according to the
> readers timezone. You can do this automagically with a view.
>
> But then, having just tried it, you can't update the view :-(
>
> So, for an example...
> create table a (timestamp date);
> create trigger a_TZ
> before update or insert on a
> for each row
> begin
> :new.timestamp:=:new.timestamp-1/24;
> end;
> /
> create view b as select a.timestamp+1/24 timestamp from a;
>
> insert into a values (SYSDATE);
> select to_char(timestamp, 'HH24:MI:SS') from a; /* Returns
> 14:29:34, ie. GMT */
> select to_char(timestamp, 'HH24:MI:SS') from b; /* Returns
> 15:29:34 ie. local time */
>
> (Of course, I've hardcoded the 1/24 which is the BST timeshift... )
>
> Since b cannot be updated, you can't use it as the base table in a
> form, unless you're willing to write your own update statements.
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 16 1998 - 03:26:42 CDT