How to Convert Timestamps to Local Timezone in PostgreSQL

The bug: grouping by UTC date instead of local date

Events are stored in UTC - which is correct. But when you cast directly to a date for grouping, PostgreSQL uses the UTC value:

-- Wrong: casts the UTC timestamp to a date
SELECT
    created_at::date AS event_date,
    COUNT(*) AS event_count
FROM events
GROUP BY created_at::date
ORDER BY event_date;

An event at 2024-03-04 23:45:00 America/New_York is stored as 2024-03-05 04:45:00 UTC. The ::date cast gives 2024-03-05 - a full day ahead of the user's local date. Events near midnight are the only ones affected, so the bug hides until someone checks closely.

The fix: AT TIME ZONE before the date cast

Convert to local time first, then cast to a date:

SELECT
    (created_at AT TIME ZONE 'America/New_York')::date AS event_date,
    COUNT(*) AS event_count
FROM events
GROUP BY (created_at AT TIME ZONE 'America/New_York')::date
ORDER BY event_date;

AT TIME ZONE shifts the timestamp to the named zone, then ::date extracts the local calendar date. The GROUP BY must use the same expression.

Parameterize the timezone

Hard-coding the timezone string works, but a better approach reads it from a variable so the same query works for any region:

import psycopg2, os

TIMEZONE = os.environ["REPORT_TIMEZONE"]  # e.g. "America/New_York"

query = """
    SELECT
        (created_at AT TIME ZONE %s)::date AS event_date,
        COUNT(*) AS event_count
    FROM events
    GROUP BY (created_at AT TIME ZONE %s)::date
    ORDER BY event_date;
"""

cur.execute(query, (TIMEZONE, TIMEZONE))
rows = cur.fetchall()

Pass the timezone name twice - once per %s placeholder. PostgreSQL's AT TIME ZONE accepts any IANA zone name (America/Chicago, Europe/London, Asia/Tokyo, etc.) and handles Daylight Saving Time transitions automatically.

How to verify the fix

Inspect raw rows to compare UTC vs local dates before and after:

SELECT
    id,
    created_at AS utc_time,
    created_at AT TIME ZONE 'America/New_York' AS eastern_time,
    created_at::date AS wrong_date,
    (created_at AT TIME ZONE 'America/New_York')::date AS correct_date
FROM events
ORDER BY created_at;

Any row where wrong_date != correct_date is an event that was mis-counted before the fix - typically events between midnight local and the UTC midnight that follows.

Store UTC, convert for display and aggregation

The rule: store timestamps in UTC everywhere. Convert to local time only at report or display time. This keeps the data portable across timezones and correct through DST changes. For Python-side conversion, zoneinfo (stdlib since 3.9) or pytz both accept the same IANA zone names PostgreSQL does.