EXTRACT function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The EXTRACT function returns the number of rows in a SQL expression.
The EXTRACT function in BigQuery will return a specific part of a DATE, DATETIME or TIMESTAMP.
Where expression is a DATE, part values can be:
EXTRACT(part FROM expression)DAYOFWEEK Returns a number 1-7 with Sunday being the first day of the week.WEEK() WEEKDAY defines the start of the week, values can be SUNDAY, MONDAY etcReturns: INT64
Where expression is a DATETIME, part values can be all of the parts used with DATE (as listed above) plus:
WITH table AS (
SELECT DATETIME(TIMESTAMP '2021-01-05 00:00:00+00', 'UTC') AS datetime
)
SELECT
datetime,
EXTRACT(WEEK FROM datetime) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM datetime) AS week_monday
FROM tableMICROSECONDMILLISECONDReturns: DATE for DATE part and TIME for TIME part.
Where expression is a TIMESTAMP, part values can be all of the parts used with DATE and DATETIME (as listed above) plus:
WITH table AS (
SELECT
CAST('2021-01-05 00:00:00+00' AS TIMESTAMP) AS datetime
)
SELECT
datetime,
EXTRACT(HOUR FROM datetime AT TIME ZONE 'America/Los_Angeles') AS hour_in_LA,
EXTRACT(HOUR FROM datetime) AS hour
FROM
tableReturns: DATETIME for DATETIME part.
| datetime | week_sunday | week_monday |
|---|---|---|
| 2021-01-05T00:00:00 | 1 | 1 |
When expression is a TIMESTAMP you an add an optional argument to specify the timezone of the output:
| datetime | hour_in_LA | hour |
|---|---|---|
| 2021-01-05T00:00:00.000Z | 16 | 0 |