Extract Calendar Parts
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 8 of 22.
EXTRACT reads a field from a date or timestamp. Its syntax is EXTRACT(field FROM value). Extracting a month returns a number from 1 through 12, not the month name.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, EXTRACT(MONTH FROM ordered_on::date) AS month_number FROM orders ORDER BY id;January orders produce 1 and February orders produce 2. A month number alone does not identify a month across years: January 2025 and January 2026 both produce 1.
Use EXTRACT for individual calendar fields; keep the year when a report spans several years.
Challenge
MediumReturn id and order_year using the year from each order date. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
2Dates and Time
Read Dates ExplicitlyDays and IntervalsExtract Calendar PartsMonth BucketsDispatch CalendarPractice on your own: PostgreSQL playground