Menu
CoddyTech

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:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in SQL Foundations

Practice on your own: PostgreSQL playground