psycopg2 vs psycopg 3 vs asyncpg: same timestamptz SELECT with session timezone America/New_York returns three different datetimes
With the session timezone set to America/New_York, select '2020-07-02 03:04:05+00'::timestamptz returned datetime(2020,7,1,23,4,5) with a fixed-offset datetime.timezone(-4h) in psycopg2, datetime(2020,7,1,23,4,5) with zoneinfo.ZoneInfo('America/New_York') in psycopg 3, and datetime(2020,7,2,3,4,5) with datetime.timezone.utc in asyncpg (utcoffset 0:00:00, even though show timezone returned America/New_York on that asyncpg connection). Wall-clock values and tzinfo types differ between drivers for the same SQL, though all three are aware and the instant is the same. Tested with psycopg2-binary 2.9.13, psycopg 3.3.9, asyncpg 0.32.0, Python 3.14.7, postgres:17-alpine in Docker. Timezone set via 'set timezone' for the psycopg drivers and server_settings={'timezone': 'America/New_York'} for asyncpg.