BigQuery timestamp functions: syntax and tested examples

TIMESTAMP: create one

SELECT
  TIMESTAMP('2026-10-06 09:30:00',
            'America/New_York'),
  -- 2026-10-06 13:30:00 UTC
  TIMESTAMP(DATETIME '2026-10-06 09:30:00',
            'America/New_York'),
  -- 2026-10-06 13:30:00 UTC
  TIMESTAMP('2026-10-06 09:30:00+02'),
  -- 2026-10-06 07:30:00 UTC
  TIMESTAMP(DATE '2026-10-06');
  -- 2026-10-06 00:00:00 UTC

TIMESTAMP(datetime_expression, time_zone) reads the value as a clock time in that zone and returns the moment. An offset written in the string, such as +02, does the same job. With neither, the value is taken as UTC.

CURRENT_TIMESTAMP() returns the present moment. It takes no time zone, because a moment has none.

AT TIME ZONE

AT TIME ZONE is not a function you can call on its own. In BigQuery it exists inside EXTRACT:

SELECT
  EXTRACT(HOUR FROM
    TIMESTAMP '2026-10-06 23:30:00+00'),
  -- 23
  EXTRACT(HOUR FROM
    TIMESTAMP '2026-10-06 23:30:00+00'
    AT TIME ZONE 'Asia/Tokyo'),
  -- 8
  EXTRACT(DATE FROM
    TIMESTAMP '2026-10-06 23:30:00+00'
    AT TIME ZONE 'Asia/Tokyo');
  -- 2026-10-07

If you come from a database where ts AT TIME ZONE 'x' returns a converted value, the BigQuery equivalents are DATETIME(ts, 'x') for the local clock time and DATE(ts, 'x') for the local date.

Timestamp to date

SELECT
  DATE(TIMESTAMP '2026-10-06 23:30:00+00'),
  -- 2026-10-06
  DATE(TIMESTAMP '2026-10-06 23:30:00+00',
       'Asia/Tokyo');
  -- 2026-10-07

DATE(timestamp_expression) gives the UTC date. DATE(timestamp_expression, time_zone) gives the local one. The same moment is two different days, so a daily report needs the zone written out, once, in the query that defines the day.

TIMESTAMP_ADD and TIMESTAMP_SUB

SELECT
  TIMESTAMP_ADD(TIMESTAMP '2026-10-06 23:30:00+00',
                INTERVAL 45 MINUTE),
  -- 2026-10-07 00:15:00 UTC
  TIMESTAMP_SUB(TIMESTAMP '2026-10-06 23:30:00+00',
                INTERVAL 7 DAY);
  -- 2026-09-29 23:30:00 UTC

The largest part these accept is DAY. There is no MONTH or YEAR for timestamps, because their length depends on the calendar and the zone. To add a month, convert to DATETIME in the zone you mean, add, and convert back.

TIMESTAMP_DIFF

SELECT
  TIMESTAMP_DIFF(
    TIMESTAMP '2026-10-06 23:30:00+00',
    TIMESTAMP '2026-10-06 21:00:00+00',
    MINUTE),
  -- 150
  TIMESTAMP_DIFF(
    TIMESTAMP '2026-10-07 00:10:00+00',
    TIMESTAMP '2026-10-06 23:50:00+00',
    DAY);
  -- 0

TIMESTAMP_DIFF returns whole units of elapsed time. Twenty minutes across midnight is 0 days. This is different from DATE_DIFF and DATETIME_DIFF, which count boundaries and would return 1 for the same pair. When a “days since” number looks off by one, check which of the three functions produced it.

TIMESTAMP_TRUNC

SELECT
  TIMESTAMP_TRUNC(
    TIMESTAMP '2026-10-06 23:30:00+00', DAY),
  -- 2026-10-06 00:00:00 UTC
  TIMESTAMP_TRUNC(
    TIMESTAMP '2026-10-06 23:30:00+00', DAY,
    'Asia/Tokyo');
  -- 2026-10-06 15:00:00 UTC

The second result is midnight in Tokyo on 7 October, shown in UTC. Truncating to DAY, WEEK or MONTH without a zone gives UTC days, which is the usual cause of hourly charts that look shifted.

FORMAT_TIMESTAMP and PARSE_TIMESTAMP

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M',
    TIMESTAMP '2026-10-06 23:30:00+00',
    'Asia/Tokyo'),
  -- 2026-10-07 08:30
  PARSE_TIMESTAMP('%Y-%m-%d %H:%M',
    '2026-10-06 09:30',
    'America/New_York');
  -- 2026-10-06 13:30:00 UTC

Both take the zone as the last argument: the zone to display in, and the zone the text was written in. STRING(ts) gives the default text form, 2026-10-06 23:30:00+00.

SAFE.TIMESTAMP and bad input

SAFE.TIMESTAMP(string_column) works and returns NULL for a value that is not a valid timestamp. That differs from SAFE.DATE and SAFE.DATETIME, which fail on a string with “SAFE with function … is not supported”.

SELECT
  SAFE.TIMESTAMP(s),
  SAFE_CAST(s AS TIMESTAMP),
  SAFE.PARSE_TIMESTAMP('%Y-%m-%d', s)
FROM UNNEST(['2026-13-01 00:00:00']) AS s;
-- NULL, NULL, NULL

Use SAFE.PARSE_TIMESTAMP when the text is not in the standard layout. Count the NULLs before relying on the column.

Unix time

SELECT
  UNIX_SECONDS(TIMESTAMP '2026-10-06 23:30:00+00'),
  -- 1791329400
  TIMESTAMP_SECONDS(1791329400),
  -- 2026-10-06 23:30:00 UTC
  TIMESTAMP_MICROS(1791329400000000);
  -- 2026-10-06 23:30:00 UTC

There are separate functions for seconds, milliseconds and microseconds in each direction. GA4’s BigQuery export stores event_timestamp in microseconds, so TIMESTAMP_MICROS(event_timestamp) is the conversion there.

Mistakes to avoid

• No time zone where one is needed: DATE(ts), TIMESTAMP_TRUNC(ts, DAY), FORMAT_TIMESTAMP and EXTRACT all default to UTC.
• Loading local times as UTC. TIMESTAMP('2026-10-06 09:30:00') asserts the value is UTC.
• The wrong Unix unit. Milliseconds read as seconds give a date far in the future; seconds read as microseconds give 1970.
• BETWEEN two dates on a timestamp column. The upper bound is midnight at the start of the last day. Use >= start AND < next_day.
• On a table partitioned by a timestamp column, wrapping the column in a function can stop partition pruning.

One definition of “day”

Timestamps are exact. Reports are not built on timestamps; they are built on days, hours and weeks derived from them, and each derivation needs a zone. If five queries derive the day five times, sooner or later one of them does it in UTC.

In OWOX Data Marts the query that turns event timestamps into reporting days is a Data Mart: your SQL, with the time zone written in it, a description and an owner, run in your warehouse. Reports read the day from that one place. OWOX does not pick the zone or convert for you. It keeps your definition as the one every report uses.

Google BigQueryTop 5 Visualization Tools for BigQuery Users in 2025July 9, 2024→Google BigQueryIs BigQuery a data warehouse or a data lake? A marketing guideOctober 6, 2026→Google BigQueryHow to connect BigQuery to Google Sheets: three waysOctober 6, 2026→

See all articles →