Firebird 4: "timezone not set or does not have a name" when TZ=UTC

[fdcastel]

Working on a new Dialect for SQLALchemy using firebird-driver I've been stuck with this error:

Datatimezone not set or does not have a name

which is being raised by this code because timestamp.tzinfo doesn't have a _timezone_ attribute.

And, indeed, the debugger shows me that it doesn't have this attribute:

But I can't see what I'm missing here. It seems a perfectly valid timestamp with a well defined timezone to me. Why is this exception being raised?

For reference: this is the test from SQLAlchemy test suite. It's a very simple one, just testing a roundtrip value to/from Firebird.

[pcisar]

Well, timezone support is quite tricky. You need to use 'get_timezone()' function (provided by firebird-driver) to create tzinfo objects. This is preferred method to obtain timezone information for construction of timezone-aware datetime.datetime and datetime.time objects. Current implementation uses dateutil.tz for timezone tzinfo objects, but adds metadata necessary to store timezone regions into database instead zoned time, and to handle offset-based timezones in format required by Firebird.

BTW, the native timezone support in Python 3.9+ (zoneinfo module) does not work with Firebird. I did try, but zoneinfo lacks necessary part that dateutil.tz has required for seamless conversion between Firebird timezones and Python timezones.

[fdcastel]

Just to be sure: this means that anyone using Firebird to store values with time zone information will need to use "special" constructed datetimes? This seems to be a very limiting factor.

In this specific case: I will need to rewrite every test which uses a datetime with timezone. e.g.:

    data = datetime.datetime(
        2012, 10, 15, 12, 57, 18, tzinfo=datetime.timezone.utc
    )

to use, instead

    from firebird.driver.types import get_timezone

    data = datetime.datetime(
        2012, 10, 15, 12, 57, 18, tzinfo=get_timezone("UTC")
    )

Instead of throwing the exception, why not just call get_timezone() to populate the attribute with whatever timezone information the passed in timestamp: datetime.datetime contains?

What I'm missing here?

[pcisar]

Well, I'm not happy with current solution either, but converting tzinfo behind the scene to required format is not that simple as it seems due to differences in various implementations and external dependencies (like ICU etc.). If anyone would create a PR with reliable implementation, I'll gladly merge it.