On 2017-11-21 18:31, Lester Caine wrote:
On 21/11/17 16:42, Mark Rotteveel wrote:
If you need that, you also need to store the actual timezone
somewhere.
PostgreSQL for example also uses an offset for timestamp with timezone
(not sure if that is required by SQL standard though).
Firebird currently works nicely simply because you have UTC time and
can
add a second field for location which will provide ACCURATE details for
displaying local time. Offset only timestamps are simply wrong for many
reasons! But we need a working tzdist source network before anyone can
reliably work with timezone data live. The fact that offsets change
year
on year make storing anything with an unqualified offset dangerous and
the version of TZ data used to create the offset is as important as the
offset itself.
Except Firebird doesn't store a timestamp in UTC, Firebird stores a time
without any zone information, so current_timestamp depends on the
current timezone of the server, and values stored depend on the
interpretation (or assigned meaning) from the client application. In
other words, if not carefully controlled, it can become a mess with
varying interpretations without having a ground truth.
A timestamp with timezone gives you more control, because you can still
interpret them in UTC. With the added benefit that a client can - if
necessary - correctly render those timestamps in its own timezone using
standard tools, or insert them with its current timezone offset, and
still have them correctly interpreted by clients using a different
timezone or normalized to UTC, etc. That is actually what PostgreSQL
does: a timestamp with timezone is normalized to UTC and stored in the
same format as a timestamp without timezone, and the offset is a config
or session-specific transformation, while a timestamp (without timezone)
would be not undergo such a transformation.
That said, I've not seen what the current SQL standard is proposing,
just what does NOT work when sourcing data from other databases.
For the applications I work with, it suffices.
Mark
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
