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

Reply via email to