PGTZ environment variable is not respected

[kigawas]

• asyncpg version: 0.27.0
• PostgreSQL version: 14.5
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Not SaaS
• Python version: 3.11.0
• Platform: macOS
• Do you use pgbouncer?: bi
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: Install by wheel
• Can the issue be reproduced under both asyncio and uvloop?: Yes

Setting PGTZ environment variable does not affect the timezone returned, for example, select now().

[eirnym]

I believe that it's better to store and receive timezone-aware date-time objects and convert to the client time zone when needed. Setting of time zone is simple enough to cover cases when support of this variable is needed

I see applications for the respect for local PGTZ environment variable, but it's minority of cases (per my observations) comparing to cases when a server can receive time zone from a client.

By the way, from documentation of PGTZ, you can set connection time zone manually with SET timezone TO … if you need this.

[kigawas]

It's postgres's official PGTZ variable, not pytz

[eirnym]

I meant PGTZ, sorry for a typo. All my arguments stay the same

[kigawas]

No matter how minor it is, bug is bug.

I also tested with psycopg3 and it does recognize the PGTZ environment variable.

[elprans]

Those environment variables are specifically for libpq. Unlike psycopg2/psycopg3, asyncpg is NOT based on libpq. We do try to maintain a certain level of compatibility with libpq, on a best effort basis. In this particular case it's tricky because PGTZ and SET timezone only affect text representaion of dates, while asyncpg uses binary I/O in which Postgres always returns as a UTC timestamp for timestamp with time zone. So, any timezone manipulation would have to be done on the client side, but that is also problematic, because supporting PGTZ would require a dependency on a timezone-capable library like dateutil, which I'm reluctant to add just for this.

The good news is that asyncpg is flexible enough to let you implement this yourself. Here's a complete example using dateutil:

import asyncio
import datetime
import os

import asyncpg
import dateutil.tz

pg_epoch_datetime_utc = datetime.datetime(
    2000, 1, 1, tzinfo=datetime.timezone.utc)

async def main():
    con = await asyncpg.connect(user='postgres')

    def tz_aware_decoder(ts_tuple):
        # Decode timestamptz as usual, microseconds
        # relative to January 1, 2000 UTC.
        microseconds = ts_tuple[0]
        seconds = microseconds // 1000000
        microseconds %= 1000000

        delta = datetime.timedelta(0, seconds, microseconds)
        dt = pg_epoch_datetime_utc + delta

        # Adjust the result to PGTZ if set
        tz_str = os.environ.get("PGTZ")
        if tz_str:
            tz = dateutil.tz.gettz(tz_str)
            dt = dt.astimezone(tz)

        return dt

    def tz_encoder(dt):
        utc_dt = dt.astimezone(datetime.timezone.utc)
        delta = utc_dt - pg_epoch_datetime_utc
        seconds = delta.days * 86400 + delta.seconds
        microseconds = delta.microseconds
        return (seconds * 1000000 + microseconds,)

    await con.set_type_codec(
        'timestamptz',
        schema='pg_catalog',
        decoder=tz_aware_decoder,
        encoder=tz_encoder,
        format='tuple',
    )

    result = await con.fetchval("SELECT now()")
    print(result)
    await con.close()

asyncio.run(main())