SQL Resources/BigQuery/EXTRACT

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>) WEEKDAY defines the start of the week, values can be SUNDAY, MONDAY etc
  • ISOWEEK
  • 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
datetime
2021-01-05T00:00:00
week_sunday
1
week_monday
1

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
datetime
2021-01-05T00:00:00.000Z
hour_in_LA
16
hour
0

Related Pages

Try a better way to write SQL in BigQuery
Write SQL together with Count.
Try a better way to write SQL in BigQuery
Write SQL together with Count.