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

Source: https://docs.querylab.io/bigquery/dates-and-times/

Updated: 2026-10-27

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

**one of each**

```sql
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
```

Result in BigQuery (1 row):

| a_date | a_datetime | a_time | a_timestamp |
| --- | --- | --- | --- |
| 2026-03-29 | 2026-03-29 01:30:00 | 01:30:00 | 2026-03-28 23:30:00 UTC |

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

`CURRENT_DATE`, `CURRENT_TIMESTAMP`, `CURRENT_DATETIME` and `CURRENT_TIME` also work without parentheses. A dry run accepts all four with no `()`:

**the four CURRENT_ functions without ()**

```sql
SELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now_utc, CURRENT_DATETIME AS now_local, CURRENT_TIME AS time_now
```

BigQuery dry run: this query processes 0 B.

The values depend on when the query runs, so this page shows only the dry run. `CURRENT_DATE` and `CURRENT_TIMESTAMP` are the same functions as `CURRENT_DATE()` and `CURRENT_TIMESTAMP()`; the parentheses are optional.

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.

**11:30 PM UTC on March 1 is already March 2 in Tokyo**

```sql
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
```

Result in BigQuery (1 row):

| event_time | new_york_time | tokyo_time | utc_date | tokyo_date |
| --- | --- | --- | --- | --- |
| 2026-03-01 23:30:00 UTC | 2026-03-01 18:30:00 | 2026-03-02 08:30:00 | 2026-03-01 | 2026-03-02 |

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

**grouping orders by day, UTC vs local**

```sql
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
```

Result in BigQuery (3 rows):

| utc_day | local_day | orders |
| --- | --- | --- |
| 2026-03-01 | 2026-03-01 | 1 |
| 2026-03-02 | 2026-03-01 | 1 |
| 2026-03-02 | 2026-03-02 | 1 |

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:

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

In a script or a [session](https://docs.querylab.io/bigquery/sessions/), you can change the default instead. After `SET @@time_zone`, `DATE(ts)`, `EXTRACT` and `FORMAT_TIMESTAMP` without a zone use that zone:

**the same instant with @@time_zone set to Tokyo**

```sql
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
```

Result in BigQuery (1 row):

| day | hour | formatted |
| --- | --- | --- |
| 2026-01-02 | 5 | 2026-01-02 05:00 |

`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:

**one UTC day of Wikipedia page views**

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

BigQuery dry run: this query processes 5.0 GB.

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:

**one Los Angeles day reads two partitions**

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

BigQuery dry run: this query processes 10.1 GB.

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](https://docs.querylab.io/bigquery/partitioning/).

## Date arithmetic

### DATE_DIFF counts boundaries

`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:

**DATE_DIFF in years and days**

```sql
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
```

Result in BigQuery (1 row):

| years | days | years_almost_one |
| --- | --- | --- |
| 1 | 1 | 0 |

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:

**month arithmetic and month boundaries**

```sql
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
```

Result in BigQuery (1 row):

| jan_31_plus_month | mar_31_minus_month | last_day_of_feb | first_day_of_feb |
| --- | --- | --- | --- |
| 2026-02-28 | 2026-02-28 | 2026-02-28 | 2026-02-01 |

`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:

**24 hours later vs the same time tomorrow**

```sql
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
```

Result in BigQuery (1 row):

| plus_24_hours | plus_1_day_wall_clock |
| --- | --- |
| 2026-03-09 02:30:00 | 2026-03-09 01:30:00 |

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

**Sunday January 4 and Monday January 5, 2026**

```sql
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
```

Result in BigQuery (2 rows):

| d | weekday | dayofweek | week_from_sunday | week_from_monday | iso_week |
| --- | --- | --- | --- | --- | --- |
| 2026-01-04 | Sunday | 1 | 2026-01-04 | 2025-12-29 | 1 |
| 2026-01-05 | Monday | 2 | 2026-01-04 | 2026-01-05 | 2 |

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

In Querylab.io a bare `WEEK` in `DATE_TRUNC`, in a `*_DIFF` function or in `EXTRACT` carries a hint, with a quick fix that replaces it with `ISOWEEK` ([Code quality](https://docs.querylab.io/writing-sql/code-quality/)).

## Mixing types

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

**TIMESTAMP > DATE**

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

BigQuery rejects this query:

```text
No matching signature for operator > for argument types: TIMESTAMP, DATE
  Signature: T1 > T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {DATE, TIMESTAMP} at [1:8]
```

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:

**comparing a TIMESTAMP with a string literal**

```sql
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
```

Result in BigQuery (1 row):

| after_feb_28 |
| --- |
| true |

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:

**parse with a zone, format in a zone**

```sql
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
```

Result in BigQuery (1 row):

| parsed_date | parsed_ts | formatted | bad_input |
| --- | --- | --- | --- |
| 2026-01-31 | 2026-01-31 17:45:00 UTC | 2026-01-31 18:45 UTC+1 | NULL |

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:

**converting epoch numbers**

```sql
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
```

Result in BigQuery (1 row):

| from_seconds | from_millis | to_seconds |
| --- | --- | --- |
| 2026-03-01 00:00:00 UTC | 2026-03-01 00:00:00 UTC | 1772323200 |

`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:

**a day per row, including empty days**

```sql
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
```

Result in BigQuery (4 rows):

| day | units |
| --- | --- |
| 2026-03-01 | 5 |
| 2026-03-02 | 0 |
| 2026-03-03 | 2 |
| 2026-03-04 | 0 |

## 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](https://docs.querylab.io/bigquery/data-types/).

See what a time zone does to DATE(timestamp). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Data types, date and time types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#date_type)

- [Timestamp functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions)

- [Date functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions)

- [Format elements](https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements)