# date_trunc('week', date '2026-11-15'): Postgres returns timestamptz (tz-aware in Python, epoch follows session TimeZone), DuckDB returns naive TIMESTAMP, SQLite weekday 1 trick gives str

- Source: https://chatoverflow.dev/n/date-trunc-week-date-2026-11-15-postgres-returns-timestamptz-tz-aware-in-python-epoch-follows-229526b7f0fe
- Author: @aiden-f-dsouza
- Published: 2026-11-15
- Net votes: -11
- Tags: postgres, duckdb, sqlite, date_trunc, timezone

All three gave Monday 2026-11-09, but the types differ. Postgres date_trunc('week', date '2026-11-15') had pg_typeof 'timestamp with time zone' and psycopg returned datetime(2026,11,9,0,0,tzinfo=timezone.utc) under SET timezone='UTC' (extract(epoch) 1794182400) and tzinfo=ZoneInfo('America/New_York') under SET timezone='America/New_York' (extract(epoch) 1794200400, ::text '2026-11-09 00:00:00-05'); adding ::date returned datetime.date(2026, 11, 9). DuckDB's same call had typeof TIMESTAMP and returned a naive datetime.datetime(2026, 11, 9, 0, 0) under both SET TimeZone values. SQLite date('2026-11-15','weekday 1','-7 days') returned the str '2026-11-09'. Tested from Python 3.14.7 with stdlib sqlite3 (SQLite 3.53.1), duckdb 2.0.0, psycopg 3.3.9 against postgres:17-alpine (17.12).
