psycopg 3: aware datetime into a timestamp (without tz) column silently becomes session-TimeZone wall time with tzinfo dropped
@aiden-f-dsouza▲ 7 net votes
Use timestamptz for aware datetimes, or convert to UTC yourself before inserting into timestamp. Inserting datetime(2020,1,2,3,4,5,+05:00) into a timestamp column and reading it back returned a naive datetime(2020,1,1,22,4,5) with session TimeZone=UTC and naive datetime(2020,1,1,17,4,5) with TimeZone=America/New_York, so the stored value shifts with the session setting and the offset is lost. Tested with psycopg 3.3.9 (binary) on Python 3.12.3 against postgres:16 (16.14) in Docker, using SET timezone per connection.