DATE, DATETIME, TIMESTAMP and time zones in BigQuery

TIMESTAMP is an instant in UTC, DATETIME is a wall-clock time with no zone, DATE is a calendar day. DATE(timestamp) uses UTC unless you pass a time zone, which is why daily reports are often off by a day.

BigQuery has four time types, and choosing between them is mostly about time zones. A TIMESTAMP is an exact instant, stored and shown in UTC. A DATETIME is a date and a wall-clock time with no time zone. A DATE is a calendar day and a TIME a time of day. The common bug is turning a TIMESTAMP into a DATE without saying which time zone’s calendar you mean: BigQuery then uses UTC.

The four types

SELECT
  DATE '2026-03-29' AS a_date,
  DATETIME '2026-03-29 01:30:00' AS a_datetime,
  TIME '01:30:00' AS a_time,
  TIMESTAMP '2026-03-29 01:30:00+02:00' AS a_timestamp

• TIMESTAMP stores an instant. The literal above says 01:30 at +02:00, and BigQuery shows it as 23:30 UTC the previous day. The offset you write is used to find the instant, then thrown away. Results always show UTC.
• DATETIME stores exactly the digits you give it. It has no zone, so it can’t be placed on the global timeline by itself.
• DATE and TIME are the two halves of a DATETIME.

Use TIMESTAMP for when things happened (events, orders, logs). Use DATETIME for local times that mean the same wall clock everywhere, like a store’s opening time, and DATE for business days.

Converting a TIMESTAMP to local time

DATETIME(ts, zone) and DATE(ts, zone) show the same instant on another zone’s clock and calendar. Use IANA zone names like Europe/Paris; they handle daylight saving, where a fixed offset like +01:00 doesn’t.

WITH events AS (SELECT TIMESTAMP '2026-03-01 23:30:00 UTC' AS event_time)
SELECT
  event_time,
  DATETIME(event_time, 'America/New_York') AS new_york_time,
  DATETIME(event_time, 'Asia/Tokyo') AS tokyo_time,
  DATE(event_time) AS utc_date,
  DATE(event_time, 'Asia/Tokyo') AS tokyo_date
FROM events

Daily reports and the UTC date

DATE(ts) with no zone gives the UTC date. For a business in California, every order after 4 or 5 PM local time lands on the next day:

WITH orders AS (
  SELECT TIMESTAMP '2026-03-01 21:00:00 America/Los_Angeles' AS ordered_at UNION ALL
  SELECT TIMESTAMP '2026-03-01 15:00:00 America/Los_Angeles' UNION ALL
  SELECT TIMESTAMP '2026-03-02 09:00:00 America/Los_Angeles'
)
SELECT
  DATE(ordered_at) AS utc_day,
  DATE(ordered_at, 'America/Los_Angeles') AS local_day,
  COUNT(*) AS orders
FROM orders
GROUP BY utc_day, local_day
ORDER BY utc_day, local_day

By UTC day there is one order on March 1 and two on March 2. By the Los Angeles calendar there are two on March 1 and one on March 2. Always pass the zone when you group by day, week or month, and when you filter a day range:

WHERE DATE(ordered_at, 'America/Los_Angeles') = '2026-03-01'

In a script or a session, you can change the default instead. After SET @@time_zone, DATE(ts), EXTRACT and FORMAT_TIMESTAMP without a zone use that zone:

SET @@time_zone = 'Asia/Tokyo';
SELECT
  DATE(TIMESTAMP '2026-01-01 20:00:00 UTC') AS day,
  EXTRACT(HOUR FROM TIMESTAMP '2026-01-01 20:00:00 UTC') AS hour,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP '2026-01-01 20:00:00 UTC') AS formatted

TIMESTAMP values in the result are still returned in UTC. But everything that converts uses the setting: CAST(ts AS STRING) prints 2026-01-02 05:00:00+09, and a literal without a zone, like TIMESTAMP '2026-01-02 05:00:00', is read as Tokyo time. A script that sets @@time_zone and is later copied into another tool without that line returns different days.

On a table partitioned by day on a TIMESTAMP column, partitions are UTC days. A filter on the UTC date reads one partition:

SELECT title
FROM `bigquery-public-data.wikipedia.pageviews_2015`
WHERE DATE(datehour) = '2015-06-01'

The same filter in Los Angeles time still prunes, but a Los Angeles day overlaps two UTC days, so BigQuery reads two partitions and the estimate doubles:

SELECT title
FROM `bigquery-public-data.wikipedia.pageviews_2015`
WHERE DATE(datehour, 'America/Los_Angeles') = '2015-06-01'

The result is still correct, since only that local day’s rows pass the filter. It just costs more to read. When most queries are in one local time zone, consider partitioning on a DATE column computed in that zone at load time.

See Partitioning.

Date arithmetic

DATE_DIFF counts boundaries, not full periods

DATE_DIFF(a, b, YEAR) counts how many year boundaries lie between the dates. One day across New Year is one year. 364 days inside the same year is zero:

SELECT
  DATE_DIFF(DATE '2026-01-01', DATE '2025-12-31', YEAR) AS years,
  DATE_DIFF(DATE '2026-01-01', DATE '2025-12-31', DAY) AS days,
  DATE_DIFF(DATE '2026-12-31', DATE '2026-01-01', YEAR) AS years_almost_one

To get someone’s age or a full-month tenure, compare the parts yourself or use DATE_DIFF in days and divide.

Adding months clamps to the month end

When the target month is shorter, DATE_ADD and DATE_SUB return its last day instead of overflowing into the next month:

SELECT
  DATE_ADD(DATE '2026-01-31', INTERVAL 1 MONTH) AS jan_31_plus_month,
  DATE_SUB(DATE '2026-03-31', INTERVAL 1 MONTH) AS mar_31_minus_month,
  LAST_DAY(DATE '2026-02-10') AS last_day_of_feb,
  DATE_TRUNC(DATE '2026-02-10', MONTH) AS first_day_of_feb

LAST_DAY and DATE_TRUNC(d, MONTH) give the month’s last and first day.

Daylight saving

Adding INTERVAL 24 HOUR to a TIMESTAMP adds exactly 24 hours of real time. Adding INTERVAL 1 DAY to a local DATETIME moves the wall clock by one day. Across a daylight-saving change the two differ by an hour. New York moved its clocks forward on March 8, 2026:

WITH t AS (SELECT TIMESTAMP '2026-03-08 01:30:00 America/New_York' AS before_switch)
SELECT
  DATETIME(TIMESTAMP_ADD(before_switch, INTERVAL 24 HOUR), 'America/New_York') AS plus_24_hours,
  DATETIME_ADD(DATETIME(before_switch, 'America/New_York'), INTERVAL 1 DAY) AS plus_1_day_wall_clock
FROM t

Weeks

DAYOFWEEK numbers Sunday as 1 and Saturday as 7. DATE_TRUNC(d, WEEK) starts weeks on Sunday. For Monday weeks use WEEK(MONDAY) or ISOWEEK, and EXTRACT(ISOWEEK ...) for ISO week numbers:

SELECT
  d,
  FORMAT_DATE('%A', d) AS weekday,
  EXTRACT(DAYOFWEEK FROM d) AS dayofweek,
  DATE_TRUNC(d, WEEK) AS week_from_sunday,
  DATE_TRUNC(d, ISOWEEK) AS week_from_monday,
  EXTRACT(ISOWEEK FROM d) AS iso_week
FROM UNNEST([DATE '2026-01-04', DATE '2026-01-05']) AS d

EXTRACT(WEEK ...) (without ISO) counts Sunday-based weeks and starts at week 0 before the year’s first Sunday. ISO weeks start on Monday, and week 1 is the week with the year’s first Thursday, so January 1 can fall in week 52 or 53 of the previous year. Use EXTRACT(ISOYEAR ...) together with ISOWEEK.

Mixing types

BigQuery doesn’t compare a TIMESTAMP with a DATE or a DATETIME. You have to convert one side:

SELECT TIMESTAMP '2026-03-01 10:00:00' > DATE '2026-03-01' AS later

Write ts > TIMESTAMP('2026-03-01', 'UTC'), or compare DATE(ts, zone) with the date. A string literal is different: BigQuery converts a literal like '2026-02-28' to the column’s type, so this works:

WITH orders AS (SELECT TIMESTAMP '2026-03-01 10:00:00' AS ordered_at)
SELECT ordered_at > '2026-02-28' AS after_feb_28
FROM orders

A string literal used as a TIMESTAMP without a zone means UTC.

Parsing and formatting

PARSE_DATE, PARSE_DATETIME and PARSE_TIMESTAMP read text with a format string. FORMAT_DATE and friends write it. The SAFE. prefix returns NULL for text that doesn’t match, instead of failing the whole query:

SELECT
  PARSE_DATE('%d/%m/%Y', '31/01/2026') AS parsed_date,
  PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2026-01-31 18:45', 'Europe/Paris') AS parsed_ts,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M %Z', TIMESTAMP '2026-01-31 17:45:00 UTC', 'Europe/Paris') AS formatted,
  SAFE.PARSE_DATE('%Y-%m-%d', 'not a date') AS bad_input

Note that %Z prints the offset as UTC+1, not a name like CET.

Unix time

Event data often stores seconds or milliseconds since 1970:

SELECT
  TIMESTAMP_SECONDS(1772323200) AS from_seconds,
  TIMESTAMP_MILLIS(1772323200000) AS from_millis,
  UNIX_SECONDS(TIMESTAMP '2026-03-01 00:00:00 UTC') AS to_seconds

TIMESTAMP_MICROS and UNIX_MICROS cover microseconds, the precision BigQuery stores.

Filling in missing days

GENERATE_DATE_ARRAY builds a calendar. Join it to the data to show days with no rows as zero:

WITH sales AS (
  SELECT DATE '2026-03-01' AS day, 5 AS units UNION ALL
  SELECT DATE '2026-03-03', 2
)
SELECT day, IFNULL(s.units, 0) AS units
FROM UNNEST(GENERATE_DATE_ARRAY('2026-03-01', '2026-03-04')) AS day
LEFT JOIN sales AS s USING (day)
ORDER BY day

Storage

DATE takes 8 bytes like the other time types, so storing a date as a DATE rather than a STRING like '2026-03-01' (12 bytes) is smaller and lets you partition on it. See Data types.