EXTRACT
Definition
The EXTRACT function in BigQuery will return a specific part of a DATE
, DATETIME
or TIMESTAMP
.
Syntax
EXTRACT(part FROM expression)
Where expression
is a DATE
, part values can be:
DAYOFWEEK
Returns a number 1-7 with Sunday being the first day of the week.DAY
DAYOFYEAR
WEEK
WEEK()
WEEKDAY defines the start of the week, values can be SUNDAY, MONDAY etcISOWEEK
MONTH
QUARTER
YEAR
ISOYEAR
Returns: INT64
Where expression
is a DATETIME
, part values can be all of the parts used with DATE
(as listed above) plus:
DATE
TIME
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
Returns: 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:
DATETIME
Returns: DATETIME
for DATETIME
part.
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 table
When expression
is a TIMESTAMP
you an add an optional argument to specify the timezone of the output:
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
table