How to Use EXTRACT in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
EXTRACT(part FROM value) pulls one field - YEAR, MONTH, DAY, HOUR, DAYOFWEEK, ISOWEEK - out of a DATE, DATETIME, TIME, or TIMESTAMP. With a TIMESTAMP, add AT TIME ZONE to extract in local time instead of UTC.
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;Grouping by a date part
SELECT
EXTRACT(ISOWEEK FROM order_date) AS iso_week,
SUM(order_total) AS revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
GROUP BY iso_week
ORDER BY iso_week;Timestamps and time zones
EXTRACT on a TIMESTAMP works in UTC unless you say otherwise. AT TIME ZONE re-anchors the extraction, which changes not just HOUR but potentially DAY, MONTH, and YEAR near midnight.
SELECT
EXTRACT(HOUR FROM created_at) AS hour_utc,
EXTRACT(HOUR FROM created_at AT TIME ZONE 'America/New_York') AS hour_local
FROM orders;DAYOFWEEK counts from Sunday
EXTRACT(DAYOFWEEK ...) returns 1 for Sunday through 7 for Saturday, which surprises anyone expecting Monday-first weeks. For ISO semantics use ISOWEEK and ISOYEAR, or FORMAT_DATE("%A", d) when you just want the weekday name. And when you want whole values rounded down to a boundary rather than a single numeric part, that is DATE_TRUNC, not EXTRACT.
SELECT
EXTRACT(DAYOFWEEK FROM DATE '2026-08-30') AS dow, -- 1 (Sunday)
FORMAT_DATE('%A', DATE '2026-08-30') AS day_name; -- SundayRun this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free