Geoff,

(I wonder if we are both saying the same thing, but I'm using "Canadian"
and your using "Auz"?)

> > I agree but want to suggest that if it is necessary for a
> > system to commonly convert datetime values between timezones
> > that storing the values as UTC is the only real solution.
> ...
>
> My point is that if you store a timestamp including it's
> time-zone you can have your cake and eat it too!

Only if you keep track of the rules which were in place when the time
value was defined/input.

That type of logic/process is beyond the scope of most systems
(including database engines).

> For comparison purposes normalisation remains possible while
> for information purposes the original information remains
> available. eg:
>
> What time of day was it when this photo was taken _at_ the
> location of the photo - not where my computer happens to be
> sitting some indefinite time later.

That is what (I believe) is referred to as "local time"

> The timestamp class I developed for this project internally
> stores the date/time data as UTC, to optimise comparisons etc,
> but also retains the time-zone in order that it can reproduce
> the original input without loss of information.

I don't think that storing the Timezone itself will help, if the
timezone definition changes.

You would need to store the full Timezone "structure/definition" (ie.
the rules for when daylight savings starts/ends) in order to reproduce
the original value.

Consider the following example:

In 2006 Eastern Daylight Savings (EDT) started 2:00am on Sunday April
2nd (first Sunday in April).

A photo taken at 1:00am that morning would have had a UTC time of 6:00am
(EDT = UTC -5)


If you applied the current EDT rules to this value the time would
incorrectly display as 2:00am.

Why, because starting in 2007 the daylight-saving changed to 2nd Sunday
in March. Since the first Sunday in April is after the time change, the
difference between UTC and EDT is 4 hours (not the original 5). So, the
6:00am UTC time would be displayed as 2:00am (6:00a - 4hours).

> Storing these values as ISO8601 strings, combined with our
> custom collation, the comparisons and indexing at the FB server
> all work as expected (compare value as normalised to UTC), but
> without the loss of data that is normal to the FB timestamp
> data-type.

Curious... data loss?

Are you referring to the fact that if you try to use a FB date, time or
datetime values in mathematical operations (i.e. 'NOW' + 1 second
(1/86400) will generate incorrect results? (I know all about that)

> The fact that time-zone details change over time makes it even
> more critical to store the actual time-zone relevant to the
> data... otherwise, in several years time, you may find it
> difficult to convert a UTC time back to the time-of-day of the
> original data even if you did store the source location.

As outlined above the timezone is not enough.


Sean