Interval functions
GoogleSQL for BigQuery supports the following interval functions.
Function list
| Name | Summary |
|---|---|
EXTRACT | Extracts part of anINTERVAL value. |
JUSTIFY_DAYS | Normalizes the day part of anINTERVAL value. |
JUSTIFY_HOURS | Normalizes the time part of anINTERVAL value. |
JUSTIFY_INTERVAL | Normalizes the day and time parts of anINTERVAL value. |
MAKE_INTERVAL | Constructs anINTERVAL value. |
EXTRACT
EXTRACT(partFROMinterval_expression)Description
Returns the value corresponding to the specified date part. Thepart must beone ofYEAR,MONTH,DAY,HOUR,MINUTE,SECOND,MILLISECOND orMICROSECOND.
Return Data Type
INTERVAL
Examples
In the following example, different parts of two intervals are extracted.
SELECTEXTRACT(YEARFROMi)ASyear,EXTRACT(MONTHFROMi)ASmonth,EXTRACT(DAYFROMi)ASday,EXTRACT(HOURFROMi)AShour,EXTRACT(MINUTEFROMi)ASminute,EXTRACT(SECOND FROMi)ASsecond,EXTRACT(MILLISECONDFROMi)ASmilli,EXTRACT(MICROSECONDFROMi)ASmicroFROMUNNEST([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 signdistributes over the hours, minutes, and seconds. For example:
SELECTEXTRACT(HOURFROMi)AShour,EXTRACT(MINUTEFROMi)ASminuteFROMUNNEST([INTERVAL'10 -12:30'DAYTOMINUTE])ASi/*------+--------+ | hour | minute | +------+--------+ | -12 | -30 | +------+--------*/When a negative sign precedes the year and month part in an interval, thenegative sign distributes over the years and months. For example:
SELECTEXTRACT(YEARFROMi)ASyear,EXTRACT(MONTHFROMi)ASmonthFROMUNNEST([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 byincrementing/decrementing the month or year part of the interval.
Return Data Type
INTERVAL
Example
SELECTJUSTIFY_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 to23:59:59.999999 by incrementing/decrementing the day part of the interval.
Return Data Type
INTERVAL
Example
SELECTJUSTIFY_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 anINTERVAL object usingINT64 valuesrepresenting the year, month, day, hour, minute, and second. All arguments areoptional,0 by default, and can benamed arguments.
Return Data Type
INTERVAL
Example
SELECTMAKE_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 | +--------------+---------------+-------------*/Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-11-24 UTC.