On 08/07/2020 10:00, Pavel Cisar wrote: > Hi all, > > 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). >
We will need to decide if we maintain things as is, will drop support from regions in TIME-TZ or will drop TIME-TZ completely. I personally think TIME-TZ with regions are a valid thing (albeit weird depending on the operations) because it fills a gap where one creates a TIME and a additional region column. TIME-TZ with offsets only (no regions) does not have the weird things by definition, but weird things will happen when converting from timestamps. > 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. > > 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. > I see two ways that time zones are handled: 1) Time zone name changes if the date is in DST or not 2) The one Firebird use, where time zone name does not change ICU does not seems to work in the 1 way. I also think not all software work like that, nor exists DST time zone names for every country/time zone. Do every software you use changes CET to CEST depending on the date? Windows? Linux? The browser? What will happen if in Python you try to create a date not in DST using the CEST time zone? Or the contrary, a date in DST using CET? The way Firebird work is that the time zone name is fixed and it has transition tables which make their effective formatting to string changes depending on its UTC value. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
