Interval functions

BigQuery supports the following INTERVAL functions.

MAKE_INTERVAL

MAKE_INTERVAL(year, month, day, hour, minute, second)

Description

Constructs an INTERVAL object using INT64 values representing the year, month, day, hour, minute, and second. All arguments are optional with default value of 0 and can be used as named arguments.

Return Data Type

INTERVAL

Example

SELECT
  MAKE_INTERVAL(1, 6, 15) AS i1,
  MAKE_INTERVAL(hour => 10, second => 20) AS i2,
  MAKE_INTERVAL(1, minute => 5, day => 2) AS i3

+--------------+---------------+-------------+
| i1           | i2            | i3          |
+--------------+---------------+-------------+
| 1-6 15 0:0:0 | 0-0 0 10:0:20 | 1-0 2 0:5:0 |
+--------------+---------------+-------------+

EXTRACT

EXTRACT(part FROM interval_expression)

Description

Returns the value corresponding to the specified date part. The part must be one of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND or MICROSECOND.

Return Data Type

INTERVAL

Example

SELECT
  EXTRACT(YEAR FROM i) AS year,
  EXTRACT(MONTH FROM i) AS month,
  EXTRACT(DAY FROM i) AS day,
  EXTRACT(HOUR FROM i) AS hour,
  EXTRACT(MINUTE FROM i) AS minute,
  EXTRACT(SECOND FROM i) AS second,
  EXTRACT(MILLISECOND FROM i) AS milli,
  EXTRACT(MICROSECOND FROM i) AS micro
FROM
  UNNEST([INTERVAL '1-2 3 4:5:6.789999' YEAR TO SECOND,
          INTERVAL '0-13 370 48:61:61' YEAR TO SECOND]) AS i

+------+-------+-----+------+--------+--------+-------+--------+
| year | month | day | hour | minute | second | milli | micro  |
+------+-------+-----+------+--------+--------+-------+--------+
| 1    | 2     | 3   | 4    | 5      | 6      | 789   | 789999 |
| 1    | 1     | 370 | 49   | 2      | 1      | 0     | 0      |
+------+-------+-----+------+--------+--------+-------+--------+

JUSTIFY_DAYS

JUSTIFY_DAYS(interval_expression)

Description

Normalizes the day part of the interval to the range from -29 to 29 by incrementing/decrementing the month or year part of the interval.

Return Data Type

INTERVAL

Example

SELECT
  JUSTIFY_DAYS(INTERVAL 29 DAY) AS i1,
  JUSTIFY_DAYS(INTERVAL -30 DAY) AS i2,
  JUSTIFY_DAYS(INTERVAL 31 DAY) AS i3,
  JUSTIFY_DAYS(INTERVAL -65 DAY) AS i4,
  JUSTIFY_DAYS(INTERVAL 370 DAY) AS i5

+--------------+--------------+-------------+---------------+--------------+
| i1           | i2           | i3          | i4            | i5           |
+--------------+--------------+-------------+---------------+--------------+
| 0-0 29 0:0:0 | -0-1 0 0:0:0 | 0-1 1 0:0:0 | -0-2 -5 0:0:0 | 1-0 10 0:0:0 |
+--------------+--------------+-------------+---------------+--------------+

JUSTIFY_HOURS

JUSTIFY_HOURS(interval_expression)

Description

Normalizes the time part of the interval to the range from -23:59:59.999999 to 23:59:59.999999 by incrementing/decrementing the day part of the interval.

Return Data Type

INTERVAL

Example

SELECT
  JUSTIFY_HOURS(INTERVAL 23 HOUR) AS i1,
  JUSTIFY_HOURS(INTERVAL -24 HOUR) AS i2,
  JUSTIFY_HOURS(INTERVAL 47 HOUR) AS i3,
  JUSTIFY_HOURS(INTERVAL -12345 MINUTE) AS i4

+--------------+--------------+--------------+-----------------+
| i1           | i2           | i3           | i4              |
+--------------+--------------+--------------+-----------------+
| 0-0 0 23:0:0 | 0-0 -1 0:0:0 | 0-0 1 23:0:0 | 0-0 -8 -13:45:0 |
+--------------+--------------+--------------+-----------------+

JUSTIFY_INTERVAL

JUSTIFY_INTERVAL(interval_expression)

Description

Normalizes the days and time parts of the interval.

Return Data Type

INTERVAL

Example

SELECT JUSTIFY_INTERVAL(INTERVAL '29 49:00:00' DAY TO SECOND) AS i

+-------------+
| i           |
+-------------+
| 0-1 1 1:0:0 |
+-------------+