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.

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_id

Session 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.

All BigQuery lessons