Re: [Firebird-devel] Crazy about time/timestamp with timezone

[Pavel Cisar]

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.

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.

But you can make the necessary mapping in the driver when you need to
 derive the zone id by defining a mapping from CEST -> CET -> id
65156.

I use new FB4 iUtil encode/decode methods to convert data from/to
buffer. These methods use string timezone names (or '+-HH:MM' offsets). 
On the other end I have Python datetime.time or datetime.datetime 
objects with tzinfo. For input parameters, I can't get the name or 
utcoffset out of datetime.time tzinfo for some timezones. I could get it 
only through some date, and if I just pick some like 2020-01-01, it will 
just screw up some time values. For datetime.datetime I have no problems 
getting the timezone name except that some names are not recognized by 
Firebird. If I would create name mapping, it would only crew up these 
values, so simple name mapping will not cut it (mind that 12:30 CET != 
12:30 CEST).

The main problem is that tzinfo I get on input as part of datetime/time 
values does not necessarily use region for timezone name, and there is 
no way at the driver level to guess it. For example for tzinfo created 
for 'Europe/Prague' I get either 'CET' or 'CEST', and the same apply for 
'Europe/Amsterdam' (but the DST switch date could differ between these 
regions). I can use utcoffset instead timezone name (conditionally when 
error is returned by iUtil function or unconditionally), but this will 
lead to lost information (offset instead zone id in database). And if 
done conditionally there is no way how to report such coercion back to 
the application.

For Jaybird I have defined a mapping between Firebird time zone ids
and the Java supported zone ids (which are basically the IANA tzdb
zone names, so the mapping is - almost - 100%, with a few I had to
manually remap to an equivalent zone name).

https://dateutil.readthedocs.io/en/stable/tz.html

All this stuff is based on standard system data definitions and
routines in standard libraries. I don't make up Python datetime
objects with timezone from thin air nor I fabricate them myself. If
any Python programmer will use standard way to get datetime with
zoneinfo from local system and pass it to the driver for storage in
database, I must handle it as is. I can't do any translations
(especially not just for some cases) in the driver. Mind that 12:30
in CET and CEST are different times in UTC, and 12:30 in
'Europe/Prague' could be also different UTC depending on the date.
In current state of affairs it means that for Python app. developer
some timezones could be stored in the database and some doesn't. It
has to be user's responsibility to provide ones that could be
stored.

I think I'm missing something and not understanding exactly what
you're stuck on.

As I said, CET is a zone that has a DST rule, that means that - for 
example - on 2020-01-01, 12:30 CET is 11:30 UTC, while at 2020-06-02,
 12:30 CET (== 12:30 CEST) is 10:30 UTC.

CET is NOT a zone with DTS rule, CET (Central European Time) is 1 hour 
ahead of Coordinated Universal Time (UTC). This time zone is in use 
during standard time in: Europe, Africa. Some places observe daylight 
saving time/summer time during the summer, and therefore use CEST 
(Central European Summer Time) in the summer. So, timezones like 
'Europe/Prague' HAVE DST rules where they switch between CET and CEST.

For example Algeria and Tunisia have CET the whole year as they don't 
switch to CEST.

See https://www.timeanddate.com/time/zones/cet


Firebird 'fixes' some of that confusion by basing such conversions at
2020-01-01.

Which is basically wrong if it does.

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

For the record:
>ts
datetime.datetime(2020, 7, 8, 20, 58, 
tzinfo=tzfile('/usr/share/zoneinfo/Europe/Amsterdam'))
>ts.tzname()
'CEST'
>ts.utcoffset()
datetime.timedelta(seconds=7200)

regards
Pavel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel