Geoff Worboys wrote:

> ...
>> Which is why - personally - I prefer simply to drop the TZ,
>> leave the timestamp as simple UTC and then provide the proper
>> data on where the timestamp was located ;)

> Since ISO8601 can easily be converted to UTC it makes sense
> (to me :-) to use that as the default... you have not lost
> anything and you may have gained something when you need it.

We are not going to agree. The point is the 'may have gained' IS also
'may have lost' because -05:00 only means anything to the location it
came from at that time. You are perfectly right that you have not lost
anything - but that is because you may not have the missing information
to loose.

Time management is well covered by 8601, but calendar management is not.
The original question was TIMESTAMP to unix epoch, and both of them are
simple times, but TIMESTAMP is upset (as Mikhail had spotted) because it
uses the LOCAL time rather than UTC and my suggestion was that you run
the server on UTC so that at least you have a standard base to work
from, not one that can change every six months. A UTC_TIMESTAMP has been
discussed and there is a UDF for it, but the starting point IS having a
fixed frame of reference at the server which has to be UTC. ( look at
the original tikiwiki code if you want an example of how to sort things
when the base time is the server time instead ;) )

I don't believe that adding TZ information was relevant in the case of
the original question. In fact unix epoch is used BECAUSE it is UTC
based. Since we introduced the rule of times being saved being
normalized to UTC life has been a lot easier on an international project
like bitweaver. Clients provide their DST setting and we can then
display things accurately from their point of view.

NOW we come to the question of identifying the TZ/DST zone that a
timestamp relates to, and yes 8601 is a quick shorthand to do that, but
personally I still store it as a timestamp + smallint rather than a long
character string. But since I actually need TZ/DST, the smallint becomes
a cross reference to the PHP database, and I can use that to give a
simple offset or a full DST tag. Times are always in order and I don't
need any complex collation to display a list of events in the order they
happened? Saving an event for six months time can be handled because you
know if you need to compensate for the hour shift. But yes you are right
that the rules for daylight saving may change! However at least one can
then look at times affected by that change and modify them if needs be.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php