Subject Re: [firebird-support] How to convert TIMESTAMP to unix timestamp (number of seconds since epoch)
Author Geoff Worboys
...
> 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 ;)

I am not about to suggest that having the time-zone stored with
the date/time will solve all the world's problems - or even
that it is always necessary and desirable. Nor will I dispute
that some developers insist on using things they do not really
understand. But that does not make time-zone detail bad.

When you are trying to calculate a timestamp for some point in
the future there are always many issues to consider, you may
end up having to have a location and a table of time-zone data
to work out what the desired timestamp will be for a
time-of-day of 10am. I strongly suggest that any "timestamp"
you use in this situation is not so much a "utc" timestamp as
it is a date+time+external-context. eg: 10am on date x given
location y. UTC has little do with it other than being a
convenient point of reference. Note that UTC is quite precise,
whereas what we are talking about with a future time-of-day
appointment is something that will automatically adapt as
things change and new information comes to light (the govt
extends dst etc etc) - and that is _not_ a UTC timestamp.

When recording historical data the timestamps are often saved
as you go (photos, audittrails, emails etc etc). If you saved
the time-zone with the timestamp as an ISO8601 or RFC822 string
then you have recorded the time-of-day it was when the
timestamp was save (client/server apps need to consider this
when recording such details). If you are then displaying that
historical data to the user you can display the original time-
of-day directly, without need to refer to location, time-zone
tables or any such mess. This is much simpler. If you also
want the location (my photography stuff also records location)
then that is all very good and handy - but you dont actually
need it in order to calculate the time-of-day.

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.

--
Geoff Worboys
Telesis Computing