Use UTC for `timestamp without time zone`

[WillPCowan]

asyncpg converts timestamps to datetime.datetime. If a column has type timestamp without time zone then the datetime object's .tzinfo is None. This means datetime implicitly treats this datetime as if it is local, and source code has to replace tzinfo for every column that has timestamp without time zone.

I'd expect that when developers specify no timezone it is intended to be UTC, otherwise timezone would be specified in the postgres datatype?

With this in mind it makes sense to provide UTC timezone type when creating datetime.datetime objects from columns that do not specify timezone (at least IMO).

[takeda]

This might seem like the right thing to do at first, but I don't believe it is correct.

TIMESTAMP WITHOUT TIME ZONE stores time without timezone given, similarly datetime() without .tzinfo does the same. Adding UTC is your assumption, but not everyone else might follow it. Python's documentation states that it is up to the programmer what zone naive object means. Whether it is UTC, local time zone or some other time zone.

I can imagine someone building a small service that is not mean to be available globally, and uses naive datetime object just runs on a local computer and uses local time (without timezone), asyncpg might then start converting that local time to UTC introducing frustrating bugs in their code.

Since the naive timestamps are assumed by you (the programmer) that they are UTC, you should inject the time zone into the object. You can do it as follows:

aware_datetime = naive_datetime.replace(tzinfo=datetime.UTC)

There's also pytz package that allows more control over time zone, but this should work with basic python.

I haven't tried to do it, but you might be able to override type conversion yourself and get the desired behavior: https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.set_type_codec

[takeda]

Just found this: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times

Looks like PostgreSQL discourages of using timestamp without time zone altogether.