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.

datetimeweek_sundayweek_monday
2021-01-05T00:00:0011

When expression is a TIMESTAMP you an add an optional argument to specify the timezone of the output:

datetimehour_in_LAhour
2021-01-05T00:00:00.000Z160
  • Dates and Times in BigQuery