Course contentsShow
← Data Science
Lesson 1039 of 2,14526. Advanced SQLFree lesson

Extracting Date Components (YEAR, MONTH, DAY)

Using EXTRACT and date part functions to pull year, month, day, hour, minute, and second from timestamps.

Extracting Date Components (YEAR, MONTH, DAY)

What you'll learn: How to pull specific parts like year, month, day, hour, minute, and second from date and timestamp columns using SQL's extraction functions.

The Core Idea

Think of a timestamp like a complete address: "123 Main Street, Springfield, IL 62701." Sometimes you don't need the full address—you just want the city or the zip code. Similarly, date and timestamp columns store complete information (like 2024-03-15 14:30:45), but you often need just one piece: the year for annual reports, the month for seasonal analysis, or the hour for traffic patterns.

SQL provides the EXTRACT() function to pull these components out cleanly. The syntax is straightforward:

EXTRACT(part FROM date_column)

Where part can be YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Basic Examples

-- Extract year from order date
SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

-- Extract multiple components
SELECT 
    created_at,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(HOUR FROM created_at) AS hour
FROM events;

Practical Applications

These extractions are invaluable for grouping and filtering:

-- Count orders by year and month
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY year, month
ORDER BY year, month;

Key Takeaway: EXTRACT() lets you pull specific date/time components from timestamp columns, enabling time-based analysis, grouping, and filtering without needing the full datetime value.