SQL Date Functions: Grouping, Filtering and Extracting Dates

Portable building blocks

EXTRACT(part FROM date) pulls out a year, month, day or hour and works the same in MySQL and PostgreSQL. Comparing dates with >= and < also works everywhere.

For ranges, prefer order_date >= '2025-02-01' AND order_date < '2025-03-01' over BETWEEN with the last day of the month: it stays correct for timestamps and month lengths.

MySQL vs PostgreSQL

Format as text: DATE_FORMAT(d, '%Y-%m') in MySQL; TO_CHAR(d, 'YYYY-MM') in PostgreSQL.

Days between dates: DATEDIFF(a, b) in MySQL; a - b in PostgreSQL.

Add an interval: DATE_ADD(d, INTERVAL 7 DAY) in MySQL; d + INTERVAL '7 days' in PostgreSQL.

Start of month: DATE_FORMAT(d, '%Y-%m-01') in MySQL; DATE_TRUNC('month', d) in PostgreSQL.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT EXTRACT(MONTH FROM order_date) AS month,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;

SELECT order_id, order_date, amount
FROM orders
WHERE order_date >= '2025-02-01' AND order_date < '2025-03-01'
ORDER BY order_date;

SELECT name, hire_date, EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) >= 2023
ORDER BY hire_date;

Common mistakes

• Grouping by month only. Grouping by month number merges January 2024 with January 2025. Group by year and month together when data spans years.
• BETWEEN with timestamps. BETWEEN '2025-02-01' AND '2025-02-28' misses orders later in the day on the 28th when the column is a timestamp.
• Mixing dialects. DATEDIFF and DATE_FORMAT fail in PostgreSQL; TO_CHAR and DATE_TRUNC fail in MySQL. Check which database the interview uses.

Practise Date Functions

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Daily Active Users (DAU)

   Compute the daily count of unique active users from raw product activity event timestamps.

   Easy✓ Solved
2. 2

   Rising Temperature

   Find all dates where the temperature was higher than the previous day using DATEDIFF.

   Easy✓ Solved
3. 3

   Active Users in Last 7 Days

   Filter rolling active accounts by comparing timestamp with CURRENT_DATE interval arithmetic.

   Easy✓ Solved
4. 4

   Amazon: Late Shipments Audit

   Identify orders delivered past their guaranteed delivery date and calculate the number of days late.

   Easy✓ Solved
5. 5

   Listing Booking Windows

   Count each listing's bookings in the last 90 days, the last 365 days and all time.

   Easy✓ Solved
6. 6

   Monthly Listing Ratings

   Average star rating for each listing in each month.

   Easy✓ Solved

Want a full set? Work through the Dates & Time study plan.

Questions people ask

How do I get the month name?

MySQL: MONTHNAME(d) or DATE_FORMAT(d, '%M'). PostgreSQL: TO_CHAR(d, 'Month').

How do I find activity in the last 7 days?

MySQL: d >= CURDATE() - INTERVAL 7 DAY. PostgreSQL: d >= CURRENT_DATE - INTERVAL '7 days'.

Related concepts

• SQL LAG and LEAD Functions Explained
• SQL Gaps and Islands: Finding Consecutive Streaks
• SQL GROUP BY Explained with Examples