TIMESTAMP, DATETIME and time zones
A daily report counts something per day, and a moment in time belongs to a day only after you pick a clock. This lesson uses learn.shop, a small online shop whose web_sessions table has one row per visit, with session_id, device and started_at, the moment the visit began.
How it works
started_at is a TIMESTAMP, one instant on the world clock. BigQuery stores and shows every TIMESTAMP in UTC. Midnight comes at a different instant in every time zone, so one visit can fall on different days: 01:36 UTC on 23 December is 20:36 on 22 December in New York. To turn a TIMESTAMP into a DATE you have to say which zone. With no zone, BigQuery uses UTC.
DATE(started_at)gives the UTC day.DATE(started_at, 'America/New_York')gives the day on a New York calendar. The zone is a name from the IANA list, such asAsia/TokyoorEurope/Paris.- Use the same expression in
SELECT,GROUP BYandWHERE. A report that groups by New York days but filters on UTC days adds or drops the sessions at the ends of the range. - A zone name follows that place's daylight saving rules. New York is 5 hours behind UTC for about a third of the year and 4 hours behind for the rest. A fixed shift such as
TIMESTAMP_SUB(started_at, INTERVAL 5 HOUR)takes the same hours off the instant all year, so it is right only while the clocks stand where it assumes. TIMESTAMP_TRUNC(started_at, DAY, 'America/New_York')returns the instant when that session's New York day began. It is still aTIMESTAMP, so BigQuery shows it in UTC.DATETIME(started_at, 'America/New_York')returns aDATETIME: a date and a clock reading with no zone attached, such as2025-12-22T20:36:35. It no longer names an instant, and BigQuery refuses to compare it with aTIMESTAMP. Keep theTIMESTAMPwhen you need the moment, and make aDATETIMEorDATEfor grouping and display.FORMAT_TIMESTAMP('%H:%M', started_at, 'Europe/Berlin')returns the Berlin clock reading as text, such as 02:36.%His the hour with two digits on a 24-hour clock and%Mthe minutes. Write'%H:00'to keep only the hour, as in 02:00.
Common mistake
GROUP BY DATE(started_at) runs without error and returns plausible numbers, but the days are UTC days. For a shop in New York, a visit at 20:36 on 22 December is counted on 23 December. The query gives no warning, so name the zone every time you turn a TIMESTAMP into a day.
Worked example
SELECT session_id, started_at,
DATE(started_at) AS utc_day,
DATE(started_at, 'America/New_York') AS new_york_day,
DATE(started_at, 'Asia/Tokyo') AS tokyo_day,
DATETIME(started_at, 'Asia/Tokyo') AS tokyo_clock
FROM `learn.shop.web_sessions`
WHERE session_id IN (1, 3)
ORDER BY session_idSession 1 started at 01:36 UTC on 23 December. That is the evening of 22 December in New York, so new_york_day is one day before utc_day.
Session 3 started at 17:22 UTC on 19 February. In Tokyo it is already 20 February, so tokyo_day is one day after utc_day. tokyo_clock is a DATETIME, the Tokyo clock reading with no zone.