Interval functions

GoogleSQL for BigQuery supports the following interval functions.

Function list

Name	Summary
`EXTRACT`	Extracts part of an `INTERVAL` value.
`JUSTIFY_DAYS`	Normalizes the day part of an `INTERVAL` value.
`JUSTIFY_HOURS`	Normalizes the time part of an `INTERVAL` value.
`JUSTIFY_INTERVAL`	Normalizes the day and time parts of an `INTERVAL` value.
`MAKE_INTERVAL`	Constructs an `INTERVAL` value.

`EXTRACT`

EXTRACT(partFROMinterval_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

Examples

In the following example, different parts of two intervals are extracted.

SELECT
EXTRACT(YEARFROMi)ASyear,
EXTRACT(MONTHFROMi)ASmonth,
EXTRACT(DAYFROMi)ASday,
EXTRACT(HOURFROMi)AShour,
EXTRACT(MINUTEFROMi)ASminute,
EXTRACT(SECOND FROMi)ASsecond,
EXTRACT(MILLISECONDFROMi)ASmilli,
EXTRACT(MICROSECONDFROMi)ASmicro
FROM
UNNEST([INTERVAL'1-2 3 4:5:6.789999'YEARTOSECOND,
INTERVAL'0-13 370 48:61:61'YEARTOSECOND])ASi
/*------+-------+-----+------+--------+--------+-------+--------+
 | year | month | day | hour | minute | second | milli | micro |
 +------+-------+-----+------+--------+--------+-------+--------+
 | 1 | 2 | 3 | 4 | 5 | 6 | 789 | 789999 |
 | 1 | 1 | 370 | 49 | 2 | 1 | 0 | 0 |
 +------+-------+-----+------+--------+--------+-------+--------*/

When a negative sign precedes the time part in an interval, the negative sign distributes over the hours, minutes, and seconds. For example:

SELECT
EXTRACT(HOURFROMi)AShour,
EXTRACT(MINUTEFROMi)ASminute
FROM
UNNEST([INTERVAL'10 -12:30'DAYTOMINUTE])ASi
/*------+--------+
 | hour | minute |
 +------+--------+
 | -12 | -30 |
 +------+--------*/

When a negative sign precedes the year and month part in an interval, the negative sign distributes over the years and months. For example:

SELECT
EXTRACT(YEARFROMi)ASyear,
EXTRACT(MONTHFROMi)ASmonth
FROM
UNNEST([INTERVAL'-22-6 10 -12:30'YEARTOMINUTE])ASi
/*------+--------+
 | year | month |
 +------+--------+
 | -22 | -6 |
 +------+--------*/

`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(INTERVAL29DAY)ASi1,
JUSTIFY_DAYS(INTERVAL-30DAY)ASi2,
JUSTIFY_DAYS(INTERVAL31DAY)ASi3,
JUSTIFY_DAYS(INTERVAL-65DAY)ASi4,
JUSTIFY_DAYS(INTERVAL370DAY)ASi5
/*--------------+--------------+-------------+---------------+--------------+
 | 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(INTERVAL23HOUR)ASi1,
JUSTIFY_HOURS(INTERVAL-24HOUR)ASi2,
JUSTIFY_HOURS(INTERVAL47HOUR)ASi3,
JUSTIFY_HOURS(INTERVAL-12345MINUTE)ASi4
/*--------------+--------------+--------------+-----------------+
 | 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

SELECTJUSTIFY_INTERVAL(INTERVAL'29 49:00:00'DAYTOSECOND)ASi
/*-------------+
 | i |
 +-------------+
 | 0-1 1 1:0:0 |
 +-------------*/

`MAKE_INTERVAL`

MAKE_INTERVAL(
[[year=>]value]
[,[month=>]value]
[,[day=>]value]
[,[hour=>]value]
[,[minute=>]value]
[,[second=>]value]
)

Description

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

Return Data Type

INTERVAL

Example

SELECT
MAKE_INTERVAL(1,6,15)ASi1,
MAKE_INTERVAL(hour=>10,second=>20)ASi2,
MAKE_INTERVAL(1,minute=>5,day=>2)ASi3
/*--------------+---------------+-------------+
 | i1 | i2 | i3 |
 +--------------+---------------+-------------+
 | 1-6 15 0:0:0 | 0-0 0 10:0:20 | 1-0 2 0:5:0 |
 +--------------+---------------+-------------*/