On 2020-07-09 09:10, Pavel Cisar wrote:
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?
Yes, three. Given '20:58:00 Europe Amsterdam' produced two storage
values: '19:58:00 UTC'@Europe/Amsterdam and '18:58:00
UTC'@Europe/Amsterdam, then converting those back through the same
DST-sensitive rule will result in :
Eg at 2020-03-01:
'19:58:00 UTC'@Europe/Amsterdam -> '20:58:00 Europe/Amsterdam'
'18:58:00 UTC'@Europe/Amsterdam -> '19:58:00 Europe/Amsterdam'
Eg at 2020-06-01:
'19:58:00 UTC'@Europe/Amsterdam -> '21:58:00 Europe/Amsterdam'
'18:58:00 UTC'@Europe/Amsterdam -> '20:58:00 Europe/Amsterdam'
So, 3 values. With the 2020-01-01 rule as a base date for conversion in
both directions, you will produce a single consistent value.
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.
Please explain exactly which aspects are not correct according to you.
If the goal is to produce a consistent value at a named zone, this is
the only way to do it in a dateless type.
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.
The rule I described was for a *TIME WITH TIME ZONE*, not for a
*TIMESTAMP WITH TIME ZONE*, so, given the limitations of *TIME WITH TIME
ZONE*, the rebasing is necessary. I don't rebase to a different date
when handling *TIMESTAMP WITH TIME ZONE* values.
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.
What I do is exactly what needs to be done to be able to consistently
reconstruct a value, so instead what you claim without base that I
consider it acceptable to have inconsistent data, that consistency is
the cornerstone of what and why I do this (and to be honest, I kind of
resent your implication that what I'm doing is shoddy work).
The only difference is that when retrieving a ZonedDateTime (which is
the only datetime type in Java that preserves named zones), I rebase to
the current-date, to have similar behaviour as what Firebird does when
casting TIME WITH TIME ZONE to a TIMESTAMP WITH TIME ZONE. On storing a
ZonedDateTime, it does the same as what happens when casting a TIMESTAMP
WITH TIME ZONE to a TIME WITH TIME ZONE.
Mark
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
