# INTERVAL and RANGE types in BigQuery

> INTERVAL stores a duration as months, days and time separately, so 36 hours stays 36 hours until you justify it. RANGE stores a start and an end-exclusive bound of dates or timestamps, with functions for overlaps, intersections and sessionizing.

Source: https://docs.querylab.io/bigquery/interval-and-range/

Updated: 2026-10-06

Two BigQuery types describe spans of time rather than points. An `INTERVAL` is a duration, like "90 minutes" or "1 year 2 months", that you can add to dates and timestamps. A `RANGE` is a span between two points, like a hotel stay from March 1 to March 5, with functions to test containment and overlap. Both save you from storing two columns and writing the comparisons yourself.

## INTERVAL

Write an interval with a number and a unit, or as a string with a date-part range, or build one from parts:

**four ways to write an interval**

```sql
SELECT
  INTERVAL 90 MINUTE AS ninety_minutes,
  INTERVAL '1-2' YEAR TO MONTH AS one_year_two_months,
  INTERVAL '3 4:05:06' DAY TO SECOND AS days_to_seconds,
  MAKE_INTERVAL(day => 10, hour => 2) AS made
```

Result in BigQuery (1 row):

| ninety_minutes | one_year_two_months | days_to_seconds | made |
| --- | --- | --- | --- |
| 0-0 0 1:30:0 | 1-2 0 0:0:0 | 0-0 3 4:5:6 | 0-0 10 2:0:0 |

Results show intervals in a fixed format, `Y-M D H:M:S`: years-months, then days, then time. `0-0 0 1:30:0` is 90 minutes.

### Months, days and time stay separate

An interval stores three parts that are not converted into each other: months, days and microseconds. 36 hours stays 36 hours, not 1 day and 12 hours, because a day isn't always 24 hours (daylight saving) and a month isn't a fixed number of days. The `JUSTIFY_` functions convert when you want them to:

**JUSTIFY_HOURS and JUSTIFY_INTERVAL**

```sql
SELECT
  INTERVAL 36 HOUR AS raw,
  JUSTIFY_HOURS(INTERVAL 36 HOUR) AS justified_hours,
  JUSTIFY_INTERVAL(INTERVAL 400 DAY) AS justified_days,
  EXTRACT(HOUR FROM INTERVAL 36 HOUR) AS hour_part
```

Result in BigQuery (1 row):

| raw | justified_hours | justified_days | hour_part |
| --- | --- | --- | --- |
| 0-0 0 36:0:0 | 0-0 1 12:0:0 | 1-1 10 0:0:0 | 36 |

`JUSTIFY_HOURS` turns each 24 hours into a day, `JUSTIFY_DAYS` each 30 days into a month, and `JUSTIFY_INTERVAL` does both. `EXTRACT(HOUR FROM INTERVAL 36 HOUR)` is 36, the hour part as stored.

### Differences between timestamps

Subtracting two timestamps gives an interval of hours and minutes, not days. `TIMESTAMP_DIFF` gives a plain number in the unit you ask for:

**26 hours 30 minutes, or 1,590 minutes**

```sql
SELECT
  TIMESTAMP '2026-03-02 12:30:00 UTC' - TIMESTAMP '2026-03-01 10:00:00 UTC' AS as_interval,
  TIMESTAMP_DIFF(TIMESTAMP '2026-03-02 12:30:00 UTC', TIMESTAMP '2026-03-01 10:00:00 UTC', MINUTE) AS minutes
```

Result in BigQuery (1 row):

| as_interval | minutes |
| --- | --- |
| 0-0 0 26:30:0 | 1590 |

For a number you can sort, sum or compare, `TIMESTAMP_DIFF` is usually what you want. An interval is better for adding the difference to another time.

### Adding intervals

Adding an interval to a timestamp works as expected. Adding one to a `DATE` returns a `DATETIME`, not a `DATE`, and month arithmetic clamps to the month end:

**DATE + INTERVAL 1 MONTH**

```sql
SELECT
  DATE '2026-01-31' + INTERVAL 1 MONTH AS date_plus_month,
  TIMESTAMP '2026-03-01 00:00:00 UTC' + INTERVAL 36 HOUR AS ts_plus_hours
```

Result in BigQuery (1 row):

| date_plus_month | ts_plus_hours |
| --- | --- |
| 2026-02-28 00:00:00 | 2026-03-02 12:00:00 UTC |

Use `DATE_ADD(d, INTERVAL 1 MONTH)` to stay a `DATE`. See [Dates and times](https://docs.querylab.io/bigquery/dates-and-times/#date-arithmetic).

## RANGE

A `RANGE<DATE>`, `RANGE<DATETIME>` or `RANGE<TIMESTAMP>` holds a start and an end. A NULL bound means unbounded on that side:

**bookings as date ranges**

```sql
WITH bookings AS (
  SELECT 'room-1' AS room, RANGE(DATE '2026-03-01', DATE '2026-03-05') AS stay UNION ALL
  SELECT 'room-1', RANGE(DATE '2026-03-04', DATE '2026-03-07') UNION ALL
  SELECT 'room-2', RANGE(DATE '2026-03-10', NULL)
)
SELECT
  room,
  RANGE_START(stay) AS check_in,
  RANGE_END(stay) AS check_out,
  RANGE_CONTAINS(stay, DATE '2026-03-04') AS here_on_mar_4
FROM bookings
```

Result in BigQuery (3 rows):

| room | check_in | check_out | here_on_mar_4 |
| --- | --- | --- | --- |
| room-1 | 2026-03-01 | 2026-03-05 | true |
| room-1 | 2026-03-04 | 2026-03-07 | true |
| room-2 | 2026-03-10 | NULL | false |

### The end is exclusive

`RANGE(DATE '2026-03-01', DATE '2026-03-05')` contains March 1 through March 4, not March 5:

**is March 5 in the stay?**

```sql
SELECT
  RANGE_CONTAINS(RANGE(DATE '2026-03-01', DATE '2026-03-05'), DATE '2026-03-05') AS end_included,
  RANGE_CONTAINS(RANGE(DATE '2026-03-01', DATE '2026-03-05'), DATE '2026-03-01') AS start_included
```

Result in BigQuery (1 row):

| end_included | start_included |
| --- | --- |
| false | true |

That suits check-out dates and time slots: back-to-back ranges like `[10:00, 11:00)` and `[11:00, 12:00)` don't overlap.

### Overlaps and intersections

`RANGE_OVERLAPS` tests whether two ranges share any point, and `RANGE_INTERSECT` returns the shared part:

**two bookings for the same room**

```sql
WITH a AS (SELECT RANGE(DATE '2026-03-01', DATE '2026-03-05') AS r1, RANGE(DATE '2026-03-04', DATE '2026-03-07') AS r2)
SELECT
  RANGE_OVERLAPS(r1, r2) AS overlaps,
  RANGE_START(RANGE_INTERSECT(r1, r2)) AS overlap_start,
  RANGE_END(RANGE_INTERSECT(r1, r2)) AS overlap_end
FROM a
```

Result in BigQuery (1 row):

| overlaps | overlap_start | overlap_end |
| --- | --- | --- |
| true | 2026-03-04 | 2026-03-05 |

This is the double-booking check, without the `a.start < b.end AND b.start < a.end` condition you would otherwise write by hand.

### Merging overlapping ranges into sessions

`RANGE_SESSIONIZE` is a table function that groups overlapping (or adjacent) ranges into sessions, per partition key:

**three visits, two sessions**

```sql
WITH visits AS (
  SELECT 'u1' AS user_id, RANGE(TIMESTAMP '2026-03-01 10:00:00', TIMESTAMP '2026-03-01 10:10:00') AS visit UNION ALL
  SELECT 'u1', RANGE(TIMESTAMP '2026-03-01 10:05:00', TIMESTAMP '2026-03-01 10:20:00') UNION ALL
  SELECT 'u1', RANGE(TIMESTAMP '2026-03-01 11:00:00', TIMESTAMP '2026-03-01 11:05:00')
)
SELECT user_id, RANGE_START(visit) AS visit_start, RANGE_START(session_range) AS session_start, RANGE_END(session_range) AS session_end
FROM RANGE_SESSIONIZE(TABLE visits, 'visit', ['user_id'])
ORDER BY visit_start
```

Result in BigQuery (3 rows):

| user_id | visit_start | session_start | session_end |
| --- | --- | --- | --- |
| u1 | 2026-03-01 10:00:00 UTC | 2026-03-01 10:00:00 UTC | 2026-03-01 10:20:00 UTC |
| u1 | 2026-03-01 10:05:00 UTC | 2026-03-01 10:00:00 UTC | 2026-03-01 10:20:00 UTC |
| u1 | 2026-03-01 11:00:00 UTC | 2026-03-01 11:00:00 UTC | 2026-03-01 11:05:00 UTC |

The first two visits overlap, so they share one session from 10:00 to 10:20; the third is its own session. `GAP_FILL` fills missing points in a time series.

## Comparing, sorting and storing

`RANGE` values can be compared with `=` and `<`: BigQuery compares the start bounds first, then the end bounds, so ranges sort by start and can be join keys. A `RANGE` column can also be a clustering column, which helps queries that filter on it. `INTERVAL` can't be used for clustering or partitioning.

Both types take 16 bytes per value. See [Data types](https://docs.querylab.io/bigquery/data-types/) and [Clustering](https://docs.querylab.io/bigquery/clustering/).

See what an INTERVAL of 36 hours does to a 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, interval type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#interval_type)

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

- [Data types, range type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#range_type)

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