Re: DATE field and daylight saving time

Interesting problem. I probably can't assist in the solution, but some aspects puzzle me so perhaps airing them will help me (and who knows... it may lead to a solution - how's that for optimism ! :-)

j.penney_at_servicepower.com wrote:

> 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.

Hmmm, you have a better understanding of your own business than I, but since this
problem is a can of worms why not insist that each customer does their conversions
themselves? Offloads the problem.
I'm comparing this to some American companies in Britain that have all their accounts performed in American Dollars (rather than in British Pounds). It makes the global position clearer, at the expense of some local conversion. UCT ("Universal Coordinated Time"?) or GMT or whatever would work well, since there are so many variations in daylight-savings-time around the world (or in different states in America if I remember correctly).

> So the dates have to be meaningful to them.
>
> For example, we have a "jobs" table with a start and end date/time.

How about a start date/time and an elapsed time then? So if you are billing by the minute, the elapsed time will always be accurate.

> 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.

I'd dismiss your conclusion (a) because in my opinion it is founded on an arguable premise. Instead of "we must" how about "we prefer". (I'm not getting annoyed here, please don't misunderstand, it's just that I think you have closed off certain avenues which may yield the solution!) Conclusion (b) reflects your notion that you alone must code this. As I suggested,
pass the obligation to the client (but help them in their task). Most folks would
understand (IMHO).

Before Greenwich Mean Time was invented in Britain, different cities had different
time zones, making rail transport timetables a nightmare! Hence the standardisation
to one country-wide time.
With the formation of the Scottish Parliament there's talk of having different time
zones between Scotland and England.... sigh!

Mungo Henning :-) Received on Fri Oct 16 1998 - 09:26:46 CDT