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.

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:

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

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 are kept separately

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:

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

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:

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

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:

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

Use DATE_ADD(d, INTERVAL 1 MONTH) to stay a DATE. See Dates and times.

RANGE

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

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

The end is exclusive

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

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

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:

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

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:

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

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 and Clustering.