On 08-07-2020 16:54, Pavel Cisar wrote:
Dne 08. 07. 20 v 15:32 Mark Rotteveel napsal(a):
The problem is how this is handled in Python standard library. It does
define only abstract base class for timezone handling
(https://docs.python.org/3.8/library/datetime.html#tzinfo-objects).
There are two implementations: packages dateutil and pytz. The pytz does
not support tzinfo for time objects at all, only for datetime
(timestamp). dateutil supports time with tzinfo, but it's practical
usability is limited to simple timezones with no timeshifts etc. I can't
even handle some of them in driver as it's not possible to get timezone
info like name or utcoffset without date part for these complex ones.
Then use 2020-01-01 as the date, because that is the date that
> Firebird itself uses for basing TIME WITH TIME ZONE derivation for
> named zones.
This was the first thing I did but then rejected the idea. It allows me
to store the TIME WITH TIMEZONE for such cases at the price that the
value is crewed for DST. I'd rather reject the value than store wrong one.
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.
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.
TIMESTAMP WITH TIMEZONE is ok.
B. Storing into database.
If a certain zone doesn't exist, you need to provide a mapping to the
appropriate zones you want to use. For example, CET *is* CEST. That
is: CET has a summertime rule, so the name CET is equivalent to CEST
on dates in summer time (CEST is just a dumb alias for CET during
summertime).
However, it is highly recommend to stop using those short form zone
identifiers as they are ambiguous. It is better to use the long form
identifiers like Europe/Prague.
And where I should get such mapping? Create and maintain it myself as
part of Firebird driver? This stuff is handled by dateutil Python
library that gets the data from system (on POSIX from description files,
on Windows from registry or API). For example on Linux (POSIX), the
'Europe/Prague' timezone actually maps to file
/usr/share/zoneinfo/Europe/Prague that contains data about this timezone
that dateutil parses and uses. In fact the 'Europe/Prague' name is not
part of this data so dateutil don't know it and can't return it, and
such timezone name is just used to find the file on filesystem when
tzinfo object is created. I also can't force dateutil to return CET
instead CEST as timezone name if the time falls to summer time. And
dateutil follows specification in POSIX zoneinfo file to get the name.
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.
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. Firebird 'fixes' some of that
confusion by basing such conversions at 2020-01-01.
Today I worked on implemented ZonedDateTime support in Jaybird (in
addition to the OffsetTime and OffsetDateTime support it already had),
while also ensuring that I apply 2020-01-01 for deriving TIME WITH TIME
ZONE values.
What I do for converting a TIME WITH TIME ZONE value to a ZonedDateTime
is this:
1. Take 2020-01-01 and combine with the UTC time value at zone UTC
2. Take value from <1> and base it on the zone or offset for the same
UTC instant
3. Take value from <2> and rebase it to the current date in the same zone
Step 3 could be left out, but it matches closest with what happens when
you cast a TIME WITH TIME ZONE value to a TIMESTAMP WITH TIME ZONE value.
For example for '20:58:00 Europe/Amsterdam' (stored as '19:58:00 UTC' +
zone id of Europe/Amsterdam):
Step 1: '2020-01-01 19:58:00 UTC'
Step 2: apply zone: '2020-01-01 20:58:00 Europe/Amsterdam'
Step 3: change date to current date: '2020-07-08 20:58:00
Europe/Amsterdam' (which is '2020-07-08 18:58:00 UTC'!)
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
When deriving an OffsetDateTime from a TIME WITH TIME ZONE the steps are
similar but has a slightly different outcome:
1. Take 2020-01-01 and combine with the UTC time value at zone UTC
2. Take value from <1> and base it on the zone or offset for the same
UTC instant
3. Take value from <2> and convert to offset based value
4. Take value from <3> and rebase to current date in the same offset
Taking the same example, that gives:
Step 1: '2020-01-01 19:58:00 UTC'
Step 2: apply zone: '2020-01-01 20:58:00 Europe/Amsterdam'
Step 3: change to offset: '2020-01-01 20:58:00+01:00'
Step 4: change date to current date: '2020-07-08 20:58:00+01:00' (note
that this is '2020-07-08 19:58:00 UTC'!)
This last one is a bit of an annoyance to me, but in this case I prefer
preserving the UTC time and allowing roundtripping of the value that
retains the same UTC value.
On save, the steps are:
1. remove date
2. derive UTC time
3. store
So for input '2020-07-08 20:58:00+01:00'
Step 1: '20:58:00+01:00'
Step 2: '19:58:00 UTC'
Step 3: store '19:58:00 UTC' + id of +01:00
When deriving an OffsetTime from a TIME WITH TIME ZONE, the steps are:
1. if offset-based, start at step <2>, otherwise step 3
2. take UTC time value
3. add offset -> done
3. Take 2020-01-01 and combine with the UTC time value at zone UTC
4. Take value from <1> and base it on the zone for the same UTC instant
5. Take value from <2> and convert to offset based value
6. remove date
So, for '20:58:00 Europe/Amsterdam':
Step 1: go to 3
Step 3: '2020-01-01 19:58:00 UTC'
Step 4: apply zone: '2020-01-01 20:58:00 Europe/Amsterdam'
Step 5: change to offset: '2020-01-01 20:58:00+01:00'
Step 6: remove date: '20:58:00+01:00'
And on save:
1. derive utc time
2. store
So for '20:58:00+01:00':
Step 1: '19:58:00 UTC'
Step 2: store '19:58:00 UTC' + zone id of +01:00
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
