EXTRACT()

Concepts covered: Date Parts

A TIMESTAMP such as 2024-03-15 14:30:45 is a single value holding 6 facts, from the year at the top down to the second. EXTRACT(field FROM value) returns the fact you name as a BIGINT, which turns the parts of a date into numbers you can filter and group by.

Here is that timestamp taken apart one field at a time, with ts standing for TIMESTAMP '2024-03-15 14:30:45'.

Each part comes back as a plain number: month 3, with no leading zero and no name. That number sorts before 12 and takes part in arithmetic, while the string 'March' sorts after 'December' and adds to nothing. EXTRACT reads a DATE the same way for its calendar parts, so EXTRACT(YEAR FROM DATE '2024-03-15') is also 2024.

Numbers versus slices of text

SUBSTRING can also pull a month out of a timestamp: cast it to text and take 2 characters starting at position 6. The 2 routes hand back different types, and only one of them goes into arithmetic without another cast.

SUBSTRING(CAST(ts AS VARCHAR), 6, 2)

• Returns '03', a VARCHAR
• Depends on the text layout the cast produces
• Needs a CAST back to INTEGER before any arithmetic

EXTRACT(MONTH FROM ts)

• Returns 3, a BIGINT
• Reads the stored value, whatever its display
• Joins arithmetic and numeric comparison as it is

Watch EXTRACT run on rows

On a table, EXTRACT runs once per row. Each call reads that row's timestamp and fills 1 new column, so the result keeps every row it started with. Order 101 landed at 23:45:10 on December 31, 2023. Its parts read year 2023 and month 12, with under 15 minutes left in the year.

SELECT
  order_id,
  ordered_at,
  EXTRACT(YEAR FROM ordered_at) AS order_year,
  EXTRACT(MONTH FROM ordered_at) AS order_month
FROM orders

Filter on a part

A part is a number, so it works in WHERE like any other column. What a single part loses is the larger parts around it. EXTRACT(MONTH FROM ordered_at) = 12 keeps December 2022 rows alongside December 2023 rows, since both read as month 12. Add the year when you mean a particular December.

Month only

Month 12 matches December of every year: 3 rows from 2 different years.

SELECT
  order_id,
  ordered_at
FROM orders
WHERE EXTRACT(MONTH FROM ordered_at) = 12

Year + month

Adding the year narrows the result to December 2023: 1 row.

SELECT
  order_id,
  ordered_at
FROM orders
WHERE EXTRACT(MONTH FROM ordered_at) = 12
  AND EXTRACT(YEAR FROM ordered_at) = 2023

That loss is sometimes the point. A report comparing every Christmas Eve filters on month 12 and day 24 and leaves the year out on purpose.

Parts inside expressions

A part is an expression like any other, so it can sit inside CASE or BETWEEN or go into plain arithmetic. Labelling each order with a meal slot takes a single CASE over EXTRACT(HOUR FROM ordered_at).

SELECT
  order_id,
  ordered_at,
  EXTRACT(HOUR FROM ordered_at) AS order_hour,
  CASE
    WHEN EXTRACT(HOUR FROM ordered_at) BETWEEN 11 AND 14 THEN 'lunch'
    WHEN EXTRACT(HOUR FROM ordered_at) BETWEEN 17 AND 21 THEN 'dinner'
    ELSE 'other'
  END AS meal_slot
FROM orders

Order 306 has no timestamp. EXTRACT of a NULL is NULL, so every WHEN test is unknown and the row falls through to 'other'. Put WHEN ordered_at IS NULL THEN 'unknown' first when missing times should stay visible.

Each field also has a function of the same name: year(ordered_at) returns what EXTRACT(YEAR FROM ordered_at) returns. The next section lists every field in both forms.

The orders table holds rows from 2022 through 2024. Which rows does this query return?

SELECT
  order_id,
  ordered_at
FROM orders
WHERE EXTRACT(MONTH FROM ordered_at) = 2
  AND EXTRACT(DAY FROM ordered_at) = 29

• February 29 orders from all 3 years, since the filter never names a year
• Only orders from February 29, 2024, the one leap day in those 3 years
• Orders from the last day of February in every year, the 28th or the 29th
• An error, because EXTRACT cannot pull a day out of a February timestamp

About this lesson

This section is part of the Dates: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.