SQL Date Functions: Extract, Truncate, and Format

Why Date Functions Matter

Date manipulation is one of the most common tasks in data science SQL. Nearly every analytical query involves filtering by date ranges, aggregating by time periods, or calculating durations. Interviewers test date functions because they reveal whether you can work with real-world data — and real-world data almost always has a time dimension.

EXTRACT: Pulling Parts from Dates

EXTRACT retrieves a specific component (year, month, day, hour, etc.) from a date or timestamp.

SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DOW FROM order_date) AS day_of_week,
    EXTRACT(QUARTER FROM order_date) AS quarter
FROM orders;

Common EXTRACT Fields

Field	Description	Example Result
YEAR	Four-digit year	2025
MONTH	Month number (1-12)	3
DAY	Day of month (1-31)	15
DOW	Day of week (0=Sun, 6=Sat)	2
HOUR	Hour (0-23)	14
QUARTER	Quarter (1-4)	1
EPOCH	Seconds since 1970-01-01	1710504000

Using EXTRACT for Analysis

-- Monthly revenue trend
SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

-- Busiest day of the week
SELECT
    EXTRACT(DOW FROM order_date) AS day_of_week,
    COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(DOW FROM order_date)
ORDER BY order_count DESC;

DATE_TRUNC: Truncating to a Time Period

DATE_TRUNC rounds a timestamp down to the specified precision. This is one of the most useful functions for time series aggregation.

SELECT
    DATE_TRUNC('month', order_date) AS month_start,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

DATE_TRUNC Precision Levels

-- Truncate to various levels
SELECT
    order_date,
    DATE_TRUNC('year', order_date) AS year_start,
    DATE_TRUNC('quarter', order_date) AS quarter_start,
    DATE_TRUNC('month', order_date) AS month_start,
    DATE_TRUNC('week', order_date) AS week_start,
    DATE_TRUNC('day', order_date) AS day_start,
    DATE_TRUNC('hour', order_date) AS hour_start
FROM orders;

For example, if order_date is 2025-03-15 14:30:00: - DATE_TRUNC('year', ...) returns 2025-01-01 00:00:00 - DATE_TRUNC('month', ...) returns 2025-03-01 00:00:00 - DATE_TRUNC('week', ...) returns 2025-03-10 00:00:00 (Monday)

EXTRACT vs DATE_TRUNC

Use EXTRACT when you need a single numeric component (the month number, the year). Use DATE_TRUNC when you need to group by a time period and want a proper date value in your results.

-- EXTRACT: returns a number (3)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-03-15 14:30:00');

-- DATE_TRUNC: returns a date (2025-03-01 00:00:00)
SELECT DATE_TRUNC('month', TIMESTAMP '2025-03-15 14:30:00');

DATE_TRUNC is preferred for GROUP BY because it preserves chronological sorting automatically.

Date Formatting

Different databases have different formatting functions:

PostgreSQL: TO_CHAR

SELECT
    TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date,
    TO_CHAR(order_date, 'Month DD, YYYY') AS long_date,
    TO_CHAR(order_date, 'Day') AS day_name,
    TO_CHAR(order_date, 'HH24:MI:SS') AS time_only
FROM orders;

MySQL: DATE_FORMAT

SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date,
    DATE_FORMAT(order_date, '%M %d, %Y') AS long_date,
    DATE_FORMAT(order_date, '%W') AS day_name
FROM orders;

Interval Arithmetic

Date arithmetic lets you add or subtract time periods from dates.

Adding and Subtracting Intervals

-- PostgreSQL syntax
SELECT
    CURRENT_DATE AS today,
    CURRENT_DATE + INTERVAL '7 days' AS next_week,
    CURRENT_DATE - INTERVAL '1 month' AS last_month,
    CURRENT_DATE + INTERVAL '1 year' AS next_year;

Calculating Differences Between Dates

-- Days between two dates
SELECT
    order_date,
    ship_date,
    ship_date - order_date AS days_to_ship
FROM orders;

-- Using AGE function (PostgreSQL)
SELECT
    AGE(CURRENT_DATE, hire_date) AS tenure
FROM employees;

-- Using DATEDIFF (MySQL)
SELECT
    DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders;

Filtering by Date Ranges

-- Orders from the last 30 days
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Orders from a specific month
SELECT *
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2025-02-01';

-- Using DATE_TRUNC for clean month filtering
SELECT *
FROM orders
WHERE DATE_TRUNC('month', order_date) = '2025-01-01';

Common Interview Patterns

Pattern 1: Year-over-Year Comparison

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(CASE
        WHEN EXTRACT(YEAR FROM order_date) = 2025
        THEN amount ELSE 0
    END) AS revenue_2025,
    SUM(CASE
        WHEN EXTRACT(YEAR FROM order_date) = 2024
        THEN amount ELSE 0
    END) AS revenue_2024
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Pattern 2: Cohort Analysis by Sign-Up Month

SELECT
    DATE_TRUNC('month', u.signup_date) AS cohort_month,
    EXTRACT(MONTH FROM AGE(o.order_date, u.signup_date)) AS months_since_signup,
    COUNT(DISTINCT u.user_id) AS active_users
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

Pattern 3: Rolling 7-Day Active Users

SELECT
    date,
    COUNT(DISTINCT user_id) AS daily_active,
    COUNT(DISTINCT user_id) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS weekly_active
FROM user_activity
GROUP BY date
ORDER BY date;

Pattern 4: Business Days Calculation

-- Count business days between two dates (PostgreSQL)
SELECT
    order_date,
    ship_date,
    (
        SELECT COUNT(*)
        FROM generate_series(order_date, ship_date - INTERVAL '1 day', '1 day') d
        WHERE EXTRACT(DOW FROM d) NOT IN (0, 6)
    ) AS business_days
FROM orders;

Database Differences

Date function syntax varies across databases. Here are key differences to be aware of:

Operation	PostgreSQL	MySQL
Current date	CURRENT_DATE	CURDATE()
Truncate	DATE_TRUNC('month', d)	DATE_FORMAT(d, '%Y-%m-01')
Extract	EXTRACT(MONTH FROM d)	EXTRACT(MONTH FROM d)
Date diff	date2 - date1	DATEDIFF(date2, date1)
Add interval	d + INTERVAL '1 day'	DATE_ADD(d, INTERVAL 1 DAY)

In interviews, if you are unsure which dialect to use, mention PostgreSQL syntax. It is the most commonly expected in data science interviews.

Practice Problems

For more date function practice, visit the date functions practice page.

Key Takeaways

EXTRACT pulls individual components from dates. DATE_TRUNC rounds dates to time period boundaries and is ideal for GROUP BY queries. Interval arithmetic handles date addition, subtraction, and difference calculations. Master these functions and you can handle virtually any time-based SQL question in an interview.