Lester Caine wrote:

> Rule 1 - Server time should always be UTC and all times saved are UTC
> Rule 2 - if you need to know the offset for a time save it separately

An alternative, if your client application can be made to
support it, is to use timestamps with embedded time-zones
stored as ISO8601 strings in the database. I recently
developed a custom collation that will sort ISO8601 strings
correctly - which means that you can store your timestamp
with a time-zone and have it sort and index correctly. To
do date manipulations in SQL you will also need appropriate
UDFs to deal with such date-as-string input values.

Having a single timestamp+timezone field makes many things
much easier - especially when the client app can make good
use of such values. (This project uses a custom timestamp
class that incorporates time-zone in the timestamp so that
you no longer have to lose information via normalisation.
This makes all sorts of sense for many applications.)

We have implemented a similar solution to get some good very
large number support out of FB too.

[The plan is that the source for these custom collations will
be made available as (free) example for FB... although it is
not yet part of the FB source.]

--
Geoff Worboys
Telesis Computing