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
(
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
Examples
In the following example, different parts of two intervals are extracted.
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 |
*------+-------+-----+------+--------+--------+-------+--------*/
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
(
HOUR
FROM
i
)
AS
hour
,
EXTRACT
(
MINUTE
FROM
i
)
AS
minute
FROM
UNNEST
(
[
INTERVAL
'10 -12:30'
DAY
TO
MINUTE
]
)
AS
i
/*------+--------*
| 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
(
YEAR
FROM
i
)
AS
year
,
EXTRACT
(
MONTH
FROM
i
)
AS
month
FROM
UNNEST
(
[
INTERVAL
'-22-6 10 -12:30'
YEAR
TO
MINUTE
]
)
AS
i
/*------+--------*
| 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
(
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 |
*-------------*/
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
)
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 |
*--------------+---------------+-------------*/
