Rule #1: Store everything in UTC. Always.

Client timestamps should be converted to UTC at ingest, tagged with the original timezone if the downstream analysis needs it. Mixing local time and UTC in the same column is the #1 source of analytic bugs.

Rule #2: Convert to local timezone only at display time.

When you say "conversions by day of week," the question is: day of week where? A user in Tokyo who orders at 11pm local orders on Tuesday. The same timestamp in UTC is Wednesday. If you aggregate UTC timestamps by day of week, you've merged Tokyo Tuesdays with Sydney Wednesdays.

The diagnostic pattern:

Know what your timestamps represent. Server-side event timestamps are almost always UTC. Client-side timestamps are almost always local. Client-reported server timestamps may have been re-timed if the client clock drifted.
Look at a sample. For a dozen rows, check the timestamp against the user's known location and activity. If a US user's login events are clustered at 3am their time, you probably have a UTC → local conversion backwards somewhere.
Histogram by hour-of-day in the raw column. If it's UTC, you'll see the bimodal pattern of US + Europe daytime activity. If it's local, you'll see a single peak around 12pm. Either is fine; mixed is the sign of trouble.

Common edge cases:

DST transitions. Spring-forward and fall-back each year produce duplicate or missing hours in local time. If your analysis windows a "last 7 days" by local time, the week containing the DST transition has 167 or 169 hours. Use UTC for windowing and convert only at display.
Half-hour and 45-min timezones. India is UTC+5:30. Nepal is UTC+5:45. Don't hardcode "integer offset from UTC" anywhere — use IANA timezone names (America/Los_Angeles, Asia/Kolkata).
Historical timezone rules. Countries change their timezone rules. Russia removed DST in 2011. A timestamp from 2010 in Moscow is interpreted differently from a timestamp in 2015. Good libraries (pytz, zoneinfo, Postgres with timezone data) handle this; rolling your own timestamp + 3 hours does not.
User timezone drift. A user in SF travels to Tokyo. Their "local time" during the trip is UTC+9, not UTC-8. If you store the user's profile timezone instead of the event's actual timezone, cohort analyses of "day of week for this user" go wrong for the entire trip.
Midnight at the edge of windows. A "DAU" defined as "any activity today" is sensitive to what "today" means. UTC midnight? PT midnight? User's local midnight? Different definitions produce different DAU counts that can't be reconciled.

Postgres-specific:

timestamp with time zone stores UTC internally and converts on read based on the session timezone. Prefer it over naive timestamp.
AT TIME ZONE 'America/Los_Angeles' converts a UTC timestamp to local. Don't confuse with the reverse conversion.

The decision tree:

Event from server → UTC.
Event from client → UTC, with original device_tz stored in a separate column.
Business window ("daily revenue") → pick a single canonical timezone for the company (usually UTC or HQ local). Document it prominently.
User-facing cohort analysis → convert to user's local timezone at query time.

How do you handle timezones in an event-stream dataset?

Draft your answer

Stuck? Peek at a hint

More data cleaning questions