---
title: "BigQuery timestamp functions: syntax and tested examples"
canonical: "https://www.owox.com/blog/articles/bigquery-timestamp-functions"
updated: "2026-10-06"
---

# BigQuery timestamp functions: syntax and tested examples

A reference to BigQuery's TIMESTAMP functions with one tested example each: creating a timestamp with a time zone, AT TIME ZONE, converting to a date, adding and subtracting, differences, truncation, formatting, parsing and Unix time.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 5 min read

![BigQuery timestamp functions: syntax and tested examples](https://cdn.owox.ai/www/webflow/696a475158f388ac21a38c72_TIMESTAMP-FUNCTIONS.png/public)

A `TIMESTAMP` in BigQuery is one exact moment, stored in UTC. Nearly every function that works on one takes an optional **time zone**, and leaving it out means UTC. This page lists the functions with results from runs in BigQuery on 6 October 2026. The example moment throughout is `2026-10-06 23:30:00 UTC`.

| To do this | Use | Result |
| --- | --- | --- |
| Local clock time to a moment | `TIMESTAMP(dt, 'America/New_York')` | `13:30 UTC` |
| A moment to a local date | `DATE(ts, 'Asia/Tokyo')` | `2026-10-07` |
| A part in a local zone | `EXTRACT(HOUR FROM ts AT TIME ZONE ...)` | `8` |
| Add or subtract | `TIMESTAMP_ADD`, `TIMESTAMP_SUB` |  |
| Elapsed time | `TIMESTAMP_DIFF` | `150` |
| Round down | `TIMESTAMP_TRUNC` |  |
| To text | `FORMAT_TIMESTAMP` | `2026-10-07 08:30` |
| From text | `PARSE_TIMESTAMP` |  |
| Unix time | `UNIX_SECONDS`, `TIMESTAMP_SECONDS` | `1791329400` |

Google’s list is the [timestamp functions reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions). For the difference from `DATETIME`, see [DATE vs DATETIME vs TIMESTAMP](/blog/articles/bigquery-date-types-date-datetime-timestamp).

**_Note:_** _Originally published in January 2024. Fully updated in October 2026 with every example re-run, and sections added on AT TIME ZONE and SAFE.TIMESTAMP._

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

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## 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 `NULL`s 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](/blog/articles/bigquery-partitioned-tables) 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](https://www.owox.com/app-signup) 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.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How does AT TIME ZONE work in BigQuery?

Only inside EXTRACT: EXTRACT(HOUR FROM ts AT TIME ZONE 'Asia/Tokyo'). It is not a standalone operator. To get a converted value, use DATETIME(ts, 'Asia/Tokyo') for the local clock time or DATE(ts, 'Asia/Tokyo') for the local date.

How do I convert a TIMESTAMP to a DATE in BigQuery?

DATE(timestamp, 'time zone'). The same moment can be two different days: DATE(TIMESTAMP '2026-10-06 23:30:00+00') is 2026-10-06 and with 'Asia/Tokyo' it is 2026-10-07. Without a zone BigQuery uses UTC.

How does TIMESTAMP\_TRUNC work in BigQuery?

TIMESTAMP\_TRUNC(timestamp, part, time\_zone) rounds down to the start of the part. Without the zone, DAY, WEEK and MONTH are truncated in UTC. With 'Asia/Tokyo', truncating 2026-10-06 23:30 UTC to DAY returns 2026-10-06 15:00 UTC, which is midnight in Tokyo.

How does TIMESTAMP\_DIFF differ from DATE\_DIFF?

TIMESTAMP\_DIFF returns whole units of elapsed time, so twenty minutes across midnight is 0 days. DATE\_DIFF and DATETIME\_DIFF count the boundaries crossed and return 1 for the same pair.

Does SAFE.TIMESTAMP work in BigQuery?

Yes. SAFE.TIMESTAMP(string) returns NULL for a value that is not a valid timestamp. This differs from SAFE.DATE and SAFE.DATETIME, which fail on a string argument. SAFE\_CAST(x AS TIMESTAMP) and SAFE.PARSE\_TIMESTAMP also return NULL on bad input.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · Top 5 Visualization Tools for BigQuery Users in 2025 · July 9, 2024](/blog/articles/bigquery-visualization-tools)

[Google BigQuery · Is BigQuery a data warehouse or a data lake? A marketing guide · October 6, 2026](/blog/articles/why-gbq-perfect-data-lake)

[Google BigQuery · How to connect BigQuery to Google Sheets: three ways · October 6, 2026](/blog/articles/bigquery-reports-in-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [DATE vs DATETIME vs TIMESTAMP](https://www.owox.com/blog/articles/bigquery-date-types-date-datetime-timestamp) — /blog/articles/bigquery-date-types-date-datetime-timestamp.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [partitioned](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google BigQuery · Top 5 Visualization Tools for BigQuery Users in 2025 · July 9, 2024](https://www.owox.com/blog/articles/bigquery-visualization-tools) — /blog/articles/bigquery-visualization-tools.md
- [Google BigQuery · Is BigQuery a data warehouse or a data lake? A marketing guide · October 6, 2026](https://www.owox.com/blog/articles/why-gbq-perfect-data-lake) — /blog/articles/why-gbq-perfect-data-lake.md
- [Google BigQuery · How to connect BigQuery to Google Sheets: three ways · October 6, 2026](https://www.owox.com/blog/articles/bigquery-reports-in-google-sheets) — /blog/articles/bigquery-reports-in-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md