How to get queries for timestamptz data return localized answers?

[nobrowser]

This is disappointing:

queries.sql

-- name: get_now
select now() ;

ipython session

In [1]: import asyncpg

In [2]: import aiosql

In [3]: dsn = 'postgres://foo@bar/baz'

In [4]: conn = await asyncpg.connect(dsn, server_settings={'TimeZone': 'America/Los_Angeles'})

In [5]: queries=aiosql.from_path('queries.sql', 'asyncpg')

In [7]: results = await queries.get_now(conn)

In [8]: results
Out[8]: [<Record now=datetime.datetime(2022, 2, 26, 0, 52, 24, 105028, tzinfo=datetime.timezone.utc)>]

... and of course it was 4:52 pm here, not 0:52 am.

So, how are we supposed to handle this? Do I have to wrangle the timezone conversion in Python code? That would make timestamptz quite useless with this library, would it not?

[elprans]

This is how PostgreSQL stores timestamps, i.e. the timezone information is not persisted and the binary I/O ignores the TimeZone setting. One way to handling this is with a custom "tuple" codec on timestamptz

[nobrowser]

So let me get this straight, when using what you term "binary I/O" (which asyncpg does, as I now understand), there is in fact no difference at all between timestamp and timestamptz?

[elprans]

There is. timestamp is a naive datetime, no timezone at all. timestamptz gets normalized to UTC internally in Postgres.

[nobrowser]

Sorry, now I am confused again. AFAIK both of these types are stored in postgres simply as suitably wide integers, so what kind of normalization can you mean? The only difference I knew about (and yes, that is a kind of normalization) was that clients (like psql and psycopg) can set the session timezone and then, for that session, timestamptz is interpreted as UTC and converted to/from the session timezone. But you are saying that asyncpg ignores the session timezone, so what difference remains?

[elprans]

The difference is that you can send a timezone-aware datetime.datetime object and the timezone will be taken into account when converting the value into internal Datum representation. In contrast, timestamp can only take timezone-naive objects and just stores and returns the value as-is.