BigQuery TIMESTAMP vs DATETIME: The Difference and When to Use Each

In BigQuery, TIMESTAMP is an absolute point in time, stored internally in UTC and tied to a specific instant anywhere on Earth, while DATETIME is a wall-clock reading, a date and time with no time zone attached at all. That single difference, whether the value carries a time zone, is what decides which you should use. Get it wrong and you get reports that are off by several hours, or events that appear to happen before they were recorded. Below is the difference in plain terms, when to use each, the DATE and TIME types alongside them, and how to convert.

The core difference: time zone or not

A TIMESTAMP answers "what instant was this, everywhere at once". A DATETIME answers "what did the clock on the wall read", with no claim about where that wall is. Both keep the same precision down to the microsecond; the only difference is the presence of a zone.

When to use TIMESTAMP

Use TIMESTAMP for anything that records a real event: a purchase, a login, a server log line, a sensor reading. These happen at one absolute instant, and you almost always have users or systems in more than one time zone. Storing them as TIMESTAMP means BigQuery holds them in UTC and you convert to a local zone only when you display them.

SELECT
  event_id,
  created_at,                                        -- stored as UTC
  DATETIME(created_at, 'America/New_York') AS et_local
FROM app.events

This is the safe default for event data. Store the absolute moment once, in UTC, and render it in whatever zone each report needs. You never lose information, because the zone is known.

When to use DATETIME

Use DATETIME when the value genuinely has no time zone, or when the zone is fixed and implied. A store's opening time of 09:00, a scheduled 14:00 meeting slot in local time, a business calendar that is the same reading regardless of where the viewer sits. Attaching a zone to those would add a meaning that is not really there.

The risk with DATETIME is using it for real events. If you store a purchase as DATETIME, you have thrown away the zone, so two purchases written "10:00" might be an hour apart in reality and you can no longer tell. When in doubt for event data, choose TIMESTAMP.

Converting between them

Conversion always involves a time zone, because that is the piece the two types disagree on. Name the zone explicitly rather than relying on a default.

-- TIMESTAMP to DATETIME: pick the zone to read the clock in
SELECT DATETIME(ts_col, 'America/Los_Angeles') FROM t;

-- DATETIME to TIMESTAMP: declare which zone the wall clock was in
SELECT TIMESTAMP(dt_col, 'America/Los_Angeles') FROM t;

-- either one down to a plain calendar date
SELECT DATE(ts_col, 'America/New_York') FROM t;

Always pass the zone string. If you omit it, BigQuery assumes UTC, and a report that silently treats Pacific-time events as UTC will land every late-evening event on the wrong day. For the wider set of formatting and math functions that work on these types, see our note on BigQuery date functions.

A common bug: grouping by day in the wrong zone

Daily counts are where the zone difference bites hardest. Truncating a UTC timestamp to a day groups by UTC midnight, which is early evening in US time zones, so a chunk of each evening's activity rolls into the next day.

-- groups by UTC day: US evening events land on the wrong day
SELECT DATE(created_at) AS d, COUNT(*) FROM app.events GROUP BY d;

-- groups by local day: correct for a US-based report
SELECT DATE(created_at, 'America/New_York') AS d, COUNT(*)
FROM app.events GROUP BY d;

The fix is to convert to the local zone before truncating to a date. Decide once which zone your reporting uses, then apply it consistently everywhere you group by day, week, or month.

Let the query pick the right type for you

Zone handling is exactly the kind of detail that turns a simple "orders per day" question into an off-by-hours argument. Agentsql connects read-only to BigQuery, turns a plain-English question into SQL that converts time zones correctly before it groups, runs it, and shows you the query so you can confirm the zone. See the BigQuery integration and the SQL query generator, then see how it works and ask your data a question in plain English.