On 28-2-2018 10:54, Lester Caine wrote:
Technically, the SQL Standard knows only one format, and that is
(slightly simplified):
YYYY-MM-DD HH24:MI:SS.FF..+/-TZH:TZHM
While this is the 'standard' it has the same fundamental flaw that it's
use in other standards has. It has no way of indicating if the OFFSET
has a daylight saving element! It has always been wrong to use TZ as the
description of the offset ... it IS only an offset.
I don't see how knowing about the daylight savings is relevant if you're
talking about a point in time. You are getting a date+time and its
offset against UTC. That is all you need to know to be able to correctly
establish that same instant in any time zone at that date.
Knowledge of the actual timezone becomes relevant if you need to do
calculations (eg now + 5 months), but then **only** knowing that
daylight savings is being applied is not sufficient: you'll need to know
the actual zone before you can make meaningful timezone-relative
calculations.
Conversion of data and normalizing to UTC is relatively easy provided
the day and month element is easy to identify, but identifying it's
timezone rules is much like deciding if it's M-D-Y or D-M-Y :(
The timezone rules only become important when you need to perform
timezone-relative calculations. For example, I'm in CET
(Europe/Amsterdam) when doing 2018-02-28 11:00+01:00 + 5 months, is the
result then 2018-07-28 11:00+01:00 (absolute) or 2018-07-28 11:00+02:00
(timezone-relative)? It depends on my business needs.
The 'problem' here is that SQL's focus is on storage (and thus: points
in time), not so much on calculations. Use of either absolute or
timezone-relative is pretty much a business/functional requirement,
which can even vary in a single application depending on the task at hand.
I guess the SQL standard didn't want to tackle that complication
(because there is no single 'correct' decision here), and choosing
offset-based storage makes it simpler, while it is still possible to do
both. This just means that timezone-relative calculations are to be
handled by applications (or database-specific extensions).
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
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
