SQL Resources
SQL
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 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
    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:

    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
    datetimehour_in_LAhour
    2021-01-05T00:00:00.000Z160

    Related Pages

  • Dates and Times in BigQuery
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.