MArk,
Dne 08. 07. 20 v 20:51 Mark Rotteveel napsal(a):
Says who? Why 2020-01-01 and not other date? Because Firebird uses it
does not make it right, it just make it consistent with Firebird. Also
mind that DTS is just one from possible time shifts for timezone.
Yes, Firebird says so. Firebird and client applications need such a rule
because otherwise it is impossible to derive a consistent value for a
TIME WITH TIME ZONE value of a named zone.
Certainly, which is the point why TIME WITH TIMEZONE is pointless. It's
inconsistent in dependency on time zone. The consistency between client
and Firebird does not make it more consistent in behavior as data type.
In the previous situation (before snapshot 4.0.0.1954), a value stored
as '20:58:00 Europe/Amsterdam' would depend on the date it was saved: if
it was saved on 2020-02-01, it would be saved as '19:58:00 UTC' + zone
id of Europe/Amsterdam. If it was saved on 2020-06-02, it would be saved
as '18:58:00 UTC' + zone id of Europe/Amsterdam.
Which actually could be desired behavior in some circumstances.
Similarly, conversion to string (or for example, conversion within
client application) would then yield three possible different values:
'19:58:00 Europe/Amsterdam', '20:58:00 Europe/Amsterdam' or '21:58:00
Europe/Amsterdam' depending on the date of retrieval.
Three, not two? What additional transition do you have in Amsterdam?
With a single, constant date to derive TIME WITH TIME ZONE values for a
named zone, a consistent rule is established to be able to derive to
correct time within the zone (that is '20:58:00 Europe/Amsterdam').
Again, it's not correct, it's just consistent/invariant accross calendar.
If you don't want that, then don't save named zones in TIME WITH TIME
ZONE (hard to do, because of CURRENT_TIME), or always use TIMESTAMP WITH
TIME ZONE, so the rules are implicitly derived from the date included in
the timestamp.
Certainly. Hope you see why TIME WITH TIMEZONE is pointless.
Similar when storing to ZonedDateTime value
1. rebase the date to 2020-01-01 2. derive UTC time 3. store
So, for '2020-07-08 20:58:00 Europe/Amsterdam':
Step 1: change date to 2020-01-01: '2020-01-01 20:58:00
Europe/Amsterdam' Step 2: derive UTC time: '19:58:00 UTC' Step 3:
store '19:58:00 UTC' + id of Europe/Amsterdam
And you think that it's always the right value? I passed your example
trough dateutil library (Python 3.8):
>from dateutil import tz
>import datetime as dt
>ts = dt.datetime(2020,7,8,20,58,0,tzinfo=tz.gettz('Europe/Amsterdam'))
>print(ts)
2020-07-08 20:58:00+02:00
>print(ts.astimezone(tz.UTC))
2020-07-08 18:58:00+00:00
It seems that right UTC time should be 18:58:00 and not 19:58:00
No, because the date is rebased onto 2020-01-01, and on 2020-01-01, the
UTC time for 20:58:00 Europe/Amsterdam *is* 19:59:00 UTC. This ensures
that the time within the zone is preserved and can be consistently
reconstructed (see also above).
What was sort off reasonable for TIME WITH TZ where date is not present,
is definitely NOT acceptable for TIMESTAMP. The date is set there, so
how you can dare to rebase it? I even gave you example from widely used
library (could bring you more if one is not enough) that returns
different results. You can certainly figure out yourself what disaster
will happen in real world if application and Firebird (drivers included)
will use different math and rules? And the worst is that such "anomaly"
happens just for some time zones.
It's also not true that without rebase it could not be correctly
reconstructed from UTC. What you do means that data stored may not came
back as the same. If you consider this as acceptable, then we have
certainly different metrics for acceptance of data storage. For me it's
a cornerstone, and a reason why it gives me headache that I can't store
named timezone to get it back on retrieval (i.e. 'Europe/Amsterdam' will
be stored and thus retrieved as CET or CEST) because such information is
not available at driver level (because it's not available in Python in
general). I need to figure out a way how developers can pass this
information to the driver when needed.
regards
Pavel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
