Store UTC, display local

The standard practice is to store every timestamp in UTC — a single, unambiguous reference — and convert to a specific local time zone only at the point where a human needs to read it, whether that's a report, a dashboard, or an export. Storing local time directly creates ambiguity the moment a business operates across more than one region, or the server hosting the database moves.

Advertisement

Converting time zones per database

convert-timezone.sql
-- PostgreSQL (also works in SQL Server 2016+)
SELECT event_timestamp AT TIME ZONE 'America/New_York' AS local_time
FROM events;

-- MySQL (requires time zone tables loaded via mysql_tzinfo_to_sql first)
SELECT CONVERT_TZ(event_timestamp, 'UTC', 'America/New_York') AS local_time
FROM events;
Always use an IANA time zone name like 'America/New_York' or 'Europe/London', never a fixed UTC offset like '-05:00'. A fixed offset is only correct for half the year in any region that observes daylight saving time — the IANA name carries the DST rules with it, a raw offset does not.

The midnight-boundary bug

midnight-boundary-bug.sql
-- Wrong: comparing a UTC timestamp directly against a local calendar date
SELECT * FROM events
WHERE event_timestamp::date = '2026-06-15';
-- This actually captures roughly 8pm Jun 14 through 8pm Jun 15 Eastern Time —
-- because UTC midnight isn't local midnight

-- Correct: convert to local time zone first, then take the date
SELECT * FROM events
WHERE (event_timestamp AT TIME ZONE 'America/New_York')::date = '2026-06-15';

This is the single most common time zone bug in daily reporting: a report that looks correct at first glance is quietly shifted by however many hours the local time zone sits away from UTC.

Advertisement

The daylight saving time bug

Once a report buckets by local calendar day, the two days each year when clocks change introduce a genuinely different day length — not a bug, but a real property of local time that catches reports off guard:

dst-day-length.sql
-- "Spring forward" day has only 23 hours of local wall-clock time
-- "Fall back" day has 25 hours — an hour repeats
SELECT
  (event_timestamp AT TIME ZONE 'America/New_York')::date AS local_day,
  COUNT(*) AS event_count
FROM events
GROUP BY 1;
-- Expect a naturally lower count on the "spring forward" day even with steady traffic

A practical daily report, done correctly

correct-daily-report.sql
SELECT
  (order_timestamp AT TIME ZONE 'America/New_York')::date AS local_order_date,
  COUNT(*)      AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Converting once, at the top of the query, keeps every downstream aggregation working against genuine local calendar days instead of UTC-shifted approximations of them.

Common mistakes

  • Storing timestamps in local time instead of UTC, creating ambiguity the moment more than one time zone or a server relocation is involved.
  • Using a fixed UTC offset instead of an IANA zone name, which silently breaks for half the year in any DST-observing region.
  • Taking ::date on a raw UTC timestamp and treating the result as the local calendar day.
  • Not accounting for the 23-hour and 25-hour DST transition days when a daily report shows an unexpected dip or spike twice a year.

Key takeaways

  • Store timestamps in UTC; convert to local time only at the point of reporting or display.
  • Use AT TIME ZONE (PostgreSQL, SQL Server 2016+) or CONVERT_TZ (MySQL) with an IANA zone name, never a fixed offset.
  • Convert to local time zone before truncating to a date — comparing a raw UTC timestamp to a local calendar date shifts the whole report.
  • DST transition days naturally have 23 or 25 local hours, which shows up as an expected dip or bump in daily totals twice a year.