On 08-07-2020 20:16, Pavel Cisar wrote:
Mark,
I use iUtil
Dne 08. 07. 20 v 17:44 Mark Rotteveel napsal(a):
The thing is, TIME WITH TIME ZONE for named time zone should be
considered at 2020-01-01, so the relevant DST rule for that date is
in effect.
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.
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.
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.
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').
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.
Sure, this can cause weirdness (especially if you have to convert a
CURRENT_TIME named zone to an offset zone value), but it is
consistent.
"Weirdness" is just a fancy name for wrong results.
Firebird 'fixes' some of that confusion by basing such conversions at
2020-01-01.
Which is basically wrong if it does.
The alternative is worse and more inconsistent and wrong. Deal with it.
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).
As I said, if you don't want that, then you shouldn't use TIME WITH TIME
ZONE.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel