SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='extract'
EXTRACT
Definition
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 etc
Returns: INT64
Where expression
is a DATETIME
, part values can be all of the parts used with DATE
(as listed above) plus:
Loading code...
MICROSECOND
MILLISECOND
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:
Loading code...
Returns: 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 |
Related Pages
- Dates and Times in BigQuery