On 08-07-2020 15:00, Pavel Cisar wrote:
I'm trying to implement support for FB 4 TIME/TIMESTAMP WITH TIMEZONE in
new Python driver, and I'm constantly hitting a stone wall with it.
The TZ support in standard datetime Python module is nice, smart and
flexible, but builtin support does not supports complex TZ with time
shifts, only simple offset ones (standard support for these is in PEP
stage, and may appear in Python 3.10 or later). The complex ones are
supported via two libraries: dateutil and pytz. The dateutil is the
preferred one.
A. Reading from database
TIME WITH TIMEZONE is pointless. It sort off works for "naive" TZ that
does not have things like summer time and other time shifts based on
date. The pytz library does not even allow you to create time with such
tz. The dateutil does, but the usability is near zero, because you can't
get offset, tz name etc from it (so also the calculations are crewed).
It is not clear to me what problem you are having here.
TIMESTAMP WITH TIMEZONE is ok.
B. Storing into database.
TIME WITH TIMEZONE is impossible to store as I can't convert it
successfully with iUtil.encodeTimeTz() because I can't get the TZ name
or utcoffset for timezones like CET/CEST out of time value to pass it to
this function. Yes, it's that convoluted, because the tzinfo object
wants to decide the return value according to DST (or other execptions)
and it needs date for that. If it can't, it returns None.
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.
TIMESTAMP WITH TIMEZONE is also busted, as Firebird does not know CEST
timezone, only CET, and I can't twist/cast/whatever the python library
to return CET when it's summer time. I could circumvent that with
utcoffset instead zone name, but that would mean lost information (store
offset instead time zone ID).
And Europe/Prague (= CET or CEST depending on DST) is probably just an
example when things get busted in other TZs. So far it's impossible to
add TZ support into Firebird driver for Python that would work for all
TZ in all cases.
The data types will be supported, but reliability and usability will
depend on actual TZ used.
As I can't see how these problems could be solved at Firebird side
(except missing TZ names like CEST), this rant is just for your
information as the state of TZ support in it will have impact on our QA
capabilities.
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.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel