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.
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.
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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-28 UTC."],[[["\u003cp\u003eGoogleSQL for BigQuery offers several interval functions for manipulating \u003ccode\u003eINTERVAL\u003c/code\u003e values, including extraction, normalization, and construction.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eEXTRACT\u003c/code\u003e function allows for the retrieval of specific parts of an \u003ccode\u003eINTERVAL\u003c/code\u003e value, such as year, month, day, hour, minute, second, millisecond, or microsecond, with negative signs properly distributed.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003eJUSTIFY_DAYS\u003c/code\u003e, \u003ccode\u003eJUSTIFY_HOURS\u003c/code\u003e, and \u003ccode\u003eJUSTIFY_INTERVAL\u003c/code\u003e functions normalize the day, time, or both parts of an \u003ccode\u003eINTERVAL\u003c/code\u003e value, ensuring they fall within defined ranges.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eMAKE_INTERVAL\u003c/code\u003e function constructs a new \u003ccode\u003eINTERVAL\u003c/code\u003e value from provided year, month, day, hour, minute, and second components, all of which are optional and default to zero if not specified.\u003c/p\u003e\n"],["\u003cp\u003eThere are 5 functions in total, that all pertain to \u003ccode\u003eINTERVAL\u003c/code\u003e values, and include the functions: \u003ccode\u003eEXTRACT\u003c/code\u003e, \u003ccode\u003eJUSTIFY_DAYS\u003c/code\u003e, \u003ccode\u003eJUSTIFY_HOURS\u003c/code\u003e, \u003ccode\u003eJUSTIFY_INTERVAL\u003c/code\u003e, and \u003ccode\u003eMAKE_INTERVAL\u003c/code\u003e.\u003c/p\u003e\n"]]],[],null,["# Interval functions\n\nGoogleSQL for BigQuery supports the following interval functions.\n\nFunction list\n-------------\n\n`EXTRACT`\n---------\n\n EXTRACT(part FROM interval_expression)\n\n**Description**\n\nReturns the value corresponding to the specified date part. The `part` must be\none of `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND` or\n`MICROSECOND`.\n\n**Return Data Type**\n\n`INTERVAL`\n\n**Examples**\n\nIn the following example, different parts of two intervals are extracted. \n\n SELECT\n EXTRACT(YEAR FROM i) AS year,\n EXTRACT(MONTH FROM i) AS month,\n EXTRACT(DAY FROM i) AS day,\n EXTRACT(HOUR FROM i) AS hour,\n EXTRACT(MINUTE FROM i) AS minute,\n EXTRACT(SECOND FROM i) AS second,\n EXTRACT(MILLISECOND FROM i) AS milli,\n EXTRACT(MICROSECOND FROM i) AS micro\n FROM\n UNNEST([INTERVAL '1-2 3 4:5:6.789999' YEAR TO SECOND,\n INTERVAL '0-13 370 48:61:61' YEAR TO SECOND]) AS i\n\n /*------+-------+-----+------+--------+--------+-------+--------*\n | year | month | day | hour | minute | second | milli | micro |\n +------+-------+-----+------+--------+--------+-------+--------+\n | 1 | 2 | 3 | 4 | 5 | 6 | 789 | 789999 |\n | 1 | 1 | 370 | 49 | 2 | 1 | 0 | 0 |\n *------+-------+-----+------+--------+--------+-------+--------*/\n\nWhen a negative sign precedes the time part in an interval, the negative sign\ndistributes over the hours, minutes, and seconds. For example: \n\n SELECT\n EXTRACT(HOUR FROM i) AS hour,\n EXTRACT(MINUTE FROM i) AS minute\n FROM\n UNNEST([INTERVAL '10 -12:30' DAY TO MINUTE]) AS i\n\n /*------+--------*\n | hour | minute |\n +------+--------+\n | -12 | -30 |\n *------+--------*/\n\nWhen a negative sign precedes the year and month part in an interval, the\nnegative sign distributes over the years and months. For example: \n\n SELECT\n EXTRACT(YEAR FROM i) AS year,\n EXTRACT(MONTH FROM i) AS month\n FROM\n UNNEST([INTERVAL '-22-6 10 -12:30' YEAR TO MINUTE]) AS i\n\n /*------+--------*\n | year | month |\n +------+--------+\n | -22 | -6 |\n *------+--------*/\n\n`JUSTIFY_DAYS`\n--------------\n\n JUSTIFY_DAYS(interval_expression)\n\n**Description**\n\nNormalizes the day part of the interval to the range from -29 to 29 by\nincrementing/decrementing the month or year part of the interval.\n\n**Return Data Type**\n\n`INTERVAL`\n\n**Example** \n\n SELECT\n JUSTIFY_DAYS(INTERVAL 29 DAY) AS i1,\n JUSTIFY_DAYS(INTERVAL -30 DAY) AS i2,\n JUSTIFY_DAYS(INTERVAL 31 DAY) AS i3,\n JUSTIFY_DAYS(INTERVAL -65 DAY) AS i4,\n JUSTIFY_DAYS(INTERVAL 370 DAY) AS i5\n\n /*--------------+--------------+-------------+---------------+--------------*\n | i1 | i2 | i3 | i4 | i5 |\n +--------------+--------------+-------------+---------------+--------------+\n | 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 |\n *--------------+--------------+-------------+---------------+--------------*/\n\n`JUSTIFY_HOURS`\n---------------\n\n JUSTIFY_HOURS(interval_expression)\n\n**Description**\n\nNormalizes the time part of the interval to the range from -23:59:59.999999 to\n23:59:59.999999 by incrementing/decrementing the day part of the interval.\n\n**Return Data Type**\n\n`INTERVAL`\n\n**Example** \n\n SELECT\n JUSTIFY_HOURS(INTERVAL 23 HOUR) AS i1,\n JUSTIFY_HOURS(INTERVAL -24 HOUR) AS i2,\n JUSTIFY_HOURS(INTERVAL 47 HOUR) AS i3,\n JUSTIFY_HOURS(INTERVAL -12345 MINUTE) AS i4\n\n /*--------------+--------------+--------------+-----------------*\n | i1 | i2 | i3 | i4 |\n +--------------+--------------+--------------+-----------------+\n | 0-0 0 23:0:0 | 0-0 -1 0:0:0 | 0-0 1 23:0:0 | 0-0 -8 -13:45:0 |\n *--------------+--------------+--------------+-----------------*/\n\n`JUSTIFY_INTERVAL`\n------------------\n\n JUSTIFY_INTERVAL(interval_expression)\n\n**Description**\n\nNormalizes the days and time parts of the interval.\n\n**Return Data Type**\n\n`INTERVAL`\n\n**Example** \n\n SELECT JUSTIFY_INTERVAL(INTERVAL '29 49:00:00' DAY TO SECOND) AS i\n\n /*-------------*\n | i |\n +-------------+\n | 0-1 1 1:0:0 |\n *-------------*/\n\n`MAKE_INTERVAL`\n---------------\n\n MAKE_INTERVAL(\n [ [ year =\u003e ] value ]\n [, [ month =\u003e ] value ]\n [, [ day =\u003e ] value ]\n [, [ hour =\u003e ] value ]\n [, [ minute =\u003e ] value ]\n [, [ second =\u003e ] value ]\n )\n\n**Description**\n\nConstructs an [`INTERVAL`](/bigquery/docs/reference/standard-sql/data-types#interval_type) object using `INT64` values\nrepresenting the year, month, day, hour, minute, and second. All arguments are\noptional, `0` by default, and can be [named arguments](/bigquery/docs/reference/standard-sql/functions-reference#named_arguments).\n\n**Return Data Type**\n\n`INTERVAL`\n\n**Example** \n\n SELECT\n MAKE_INTERVAL(1, 6, 15) AS i1,\n MAKE_INTERVAL(hour =\u003e 10, second =\u003e 20) AS i2,\n MAKE_INTERVAL(1, minute =\u003e 5, day =\u003e 2) AS i3\n\n /*--------------+---------------+-------------*\n | i1 | i2 | i3 |\n +--------------+---------------+-------------+\n | 1-6 15 0:0:0 | 0-0 0 10:0:20 | 1-0 2 0:5:0 |\n *--------------+---------------+-------------*/"]]
