On 28/02/18 10:29, Mark Rotteveel wrote:
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.
This is precisely the point here ... I have a meeting 9AM local time
today, but it's postponed to next week ... if all I have is the CURRENT
time offset then it's anybodies guess what the offset will be next week?
It is PURELY a time offset ... it is NOT the timezone and so should not
be called that. Any addition of an offset facility to a stored date is
just that OFFSET not TIMEZONE ...
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).
ADD ... we need to also know just what set of rules are being used, and
this is an area where tzdist has been finalised, but there is no
official source of the data. tz ONLY provides a current set of rules
valid for dates after 1970, but if we are trying to store historic data
normalized to UTC time for easy timeline generation, then we need to
know which version of the rules were used for normalising, and if the
current rules are different to that.
Moving that to a current timeframe, if a meeting is set up with a UTC
time for international users and the local DST rules change at short
notice ... 'offset-based storage' just makes things worse so why
introduce that confusion into the equation at all? Given the number of
areas that are actively discussing changing or even dropping 'daylight
saving' the problem is enough of a minefield already.
--
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
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
------------------------------------------------------------------------------
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
