SQL Dates and Times: Filter and Group by Month

Q: How do I select rows from the last 7 days?

Compare with today's date minus seven days: WHERE order_date >= date('now', '-7 days') in SQLite, WHERE order_date >= CURRENT_DATE - INTERVAL '7 days' in PostgreSQL and WHERE order_date >= CURDATE() - INTERVAL 7 DAY in MySQL. Decide whether "last 7 days" should include today.

Q: How do I calculate the number of days between two dates?

In SQLite, subtract Julian day numbers: julianday('2024-03-10') - julianday('2024-03-01') gives 9. In PostgreSQL, subtracting two DATE values gives the number of days directly, and MySQL has DATEDIFF(later, earlier).

Q: How do I group results by week?

PostgreSQL has DATE_TRUNC('week', order_date), which gives the Monday that starts each week. SQLite can use strftime('%Y-%W', order_date) for year and week number, and MySQL has YEARWEEK(order_date). Week numbering rules differ between functions, so check which day each one treats as the start of a week.

Q: Should dates ever be stored as text?

Only in the ISO format, YYYY-MM-DD, which sorts and compares correctly as text and is how SQLite usually stores dates. Never store dates in local formats such as 05/07/2024: they sort wrongly, and it's unclear whether that's 5 July or 7 May. Prefer a real DATE or TIMESTAMP type where the database has one.

Q: Should I use DATETIME or TIMESTAMP in MySQL?

TIMESTAMP converts values to UTC for storage and back to the session's time zone when read, and its range ends in 2038. DATETIME stores the value exactly as given, without time zone conversion, and covers a much wider range. Many teams use DATETIME with values always written in UTC.

"How many orders did we get last month?" "Which customers haven't bought anything for 90 days?" "What's our revenue per week?" Some of the most common business questions are about time, and answering them means filtering, grouping and calculating with dates.

You met a few date functions in the functions lesson. This lesson goes further. Dates are where database systems differ the most, so each technique is shown in SQLite, with the MySQL and PostgreSQL spellings alongside. The ideas are identical everywhere; only the function names change.

You'll learn how dates are stored, how to filter a range without missing rows, how to pull out the year, month or weekday, how to group results by month, and how to add time and measure the gap between two dates.

Date types and the current date

Type	Holds	Example
`DATE`	A calendar date	`2024-07-05`
`TIME`	A time of day	`14:30:00`
`TIMESTAMP` / `DATETIME`	Date and time	`2024-07-05 14:30:00`

Always write dates as YYYY-MM-DD. It's the international standard, every database understands it, and it sorts correctly even when stored as text, which is how SQLite stores dates.

	SQLite	MySQL	PostgreSQL
Today	`date('now')`	`CURDATE()`	`CURRENT_DATE`
Now	`datetime('now')`	`NOW()`	`NOW()`

Filtering date ranges

Compare dates with the normal operators. For a range, the safest pattern is inclusive start, exclusive end:

SQL

WHERE order_date >= '2024-07-01'
  AND order_date <  '2024-08-01'    -- all of July

This works whether the column holds dates or full timestamps. BETWEEN '2024-07-01' AND '2024-07-31' looks simpler, but for a timestamp column it misses everything after midnight on the 31st, because '2024-07-31 15:00' is greater than '2024-07-31'.

Extracting parts and grouping by month

Get	SQLite	MySQL	PostgreSQL
Year	`strftime('%Y', d)`	`YEAR(d)`	`EXTRACT(YEAR FROM d)`
Year and month	`strftime('%Y-%m', d)`	`DATE_FORMAT(d, '%Y-%m')`	`TO_CHAR(d, 'YYYY-MM')`
Weekday	`strftime('%w', d)` (0 = Sunday)	`DAYOFWEEK(d)` (1 = Sunday)	`EXTRACT(DOW FROM d)` (0 = Sunday)

To report per month, group by the year-and-month value, not just the month number. Otherwise January 2023 and January 2024 end up in the same group.

Adding time and measuring gaps

Task	SQLite	MySQL	PostgreSQL
Add 3 days	`date(d, '+3 days')`	`DATE_ADD(d, INTERVAL 3 DAY)`	`d + INTERVAL '3 days'`
Days between	`julianday(b) - julianday(a)`	`DATEDIFF(b, a)`	`b - a`
Start of month	`date(d, 'start of month')`	`DATE_FORMAT(d, '%Y-%m-01')`	`DATE_TRUNC('month', d)`

Adding months has a catch: there's no 31 February. Each database handles it differently, so test month arithmetic on the last days of a month before relying on it.

Time zones and timestamps

A timestamp without a time zone, such as 2024-07-05 14:30, doesn't say where it's 14:30. When users or servers are in different places, store moments in UTC and convert to local time only for display.

Database	Timestamp type that handles zones
PostgreSQL	`TIMESTAMPTZ` stores an exact moment and shows it in the session's time zone
MySQL	`TIMESTAMP` converts to UTC for storage; `DATETIME` stores exactly what you give it
SQLite	No time zone type; store UTC text such as `2024-07-05 13:30:00` and convert in the application

Calendar dates without a time, such as birthdays and delivery days, don't need a zone at all, so a plain DATE is the right type for them.

SQL Dates Syntax

Date patterns (SQLite)

date('now')
WHERE d >= '2024-07-01' AND d < '2024-08-01'
strftime('%Y-%m', d)          -- year and month
date(d, '+7 days')            -- add time
julianday(b) - julianday(a)   -- days between
GROUP BY strftime('%Y-%m', d)

SQL Dates Summary

Store dates as DATE or TIMESTAMP in YYYY-MM-DD format.
Filter ranges with >= start AND < end to include every moment of the last day.
Extract the year, month or weekday with strftime, EXTRACT or YEAR depending on the database.
Group by year and month together for monthly reports.
Add intervals and measure gaps with date(), DATE_ADD or INTERVAL; store times in UTC.

SQL Dates Examples

Each example shows the code, the output it produces and what each part does.

Example 1

New customers per year

SQL

SELECT strftime('%Y', joined) AS year,
       COUNT(*) AS new_customers
FROM customers
GROUP BY year
ORDER BY year;

Output

year | new_customers
-----+--------------
2023 | 2
2024 | 3

Line-by-line explanation

Line 1Pull the year out of each joining date. MySQL: YEAR(joined). PostgreSQL: EXTRACT(YEAR FROM joined).
Line 4One row per year. For a monthly report, use '%Y-%m' instead.

Example 2

How long has each customer been with us?

SQL

SELECT name,
       joined,
       CAST(julianday('2024-07-31') - julianday(joined) AS INTEGER)
         AS days_as_customer
FROM customers
ORDER BY days_as_customer DESC;

Output

name  | joined     | days_as_customer
------+------------+-----------------
Ana   | 2023-02-10 | 537
Ben   | 2023-05-22 | 436
Chloe | 2024-01-03 | 210
Dev   | 2024-03-15 | 138
Ema   | 2024-06-30 | 31

Line-by-line explanation

Line 3julianday turns a date into a day number, so subtracting gives the gap in days. A fixed date keeps the example repeatable; in a real report use date('now').
Line 3MySQL: DATEDIFF('2024-07-31', joined). PostgreSQL: DATE '2024-07-31' - joined.
Line 6Longest-standing customers first.

Example 3

Delivery dates and weekdays

SQL

SELECT id,
       order_date,
       date(order_date, '+3 days') AS deliver_by,
       strftime('%w', order_date)  AS weekday_number
FROM orders
WHERE order_date >= '2024-07-01'
  AND order_date <  '2024-07-06';

Output

id | order_date | deliver_by | weekday_number
---+------------+------------+---------------
1  | 2024-07-01 | 2024-07-04 | 1
2  | 2024-07-02 | 2024-07-05 | 2
3  | 2024-07-02 | 2024-07-05 | 2
4  | 2024-07-05 | 2024-07-08 | 5

Line-by-line explanation

Line 3Add three days to estimate delivery.
Line 40 is Sunday, so 1 is Monday and 5 is Friday.
Lines 6 to 7Start included, end excluded: orders from 1 to 5 July.

Common SQL Dates Mistakes

Grouping by month number only

Don't

GROUP BY strftime('%m', order_date)      -- mixes years together

GROUP BY strftime('%Y-%m', order_date)

BETWEEN on timestamps

Don't

WHERE created_at BETWEEN '2024-07-01' AND '2024-07-31'
-- misses everything after 00:00 on 31 July

WHERE created_at >= '2024-07-01' AND created_at < '2024-08-01'

Dates in a local format

Don't

INSERT INTO orders (order_date) VALUES ('05/07/2024');   -- 5 July or 7 May?

INSERT INTO orders (order_date) VALUES ('2024-07-05');

SQL Dates in Real Projects

Dates in reporting

Business dashboards are built on date queries: sales per day, sign-ups per week, revenue per month compared with the same month last year. Subscription services find customers whose renewal date is within the next 7 days, and support teams find tickets older than 48 hours. Combined with window functions, date grouping produces running totals and month-on-month growth.

SQL Dates FAQ

How do I select rows from the last 7 days?

Compare with today's date minus seven days: WHERE order_date >= date('now', '-7 days') in SQLite, WHERE order_date >= CURRENT_DATE - INTERVAL '7 days' in PostgreSQL and WHERE order_date >= CURDATE() - INTERVAL 7 DAY in MySQL. Decide whether "last 7 days" should include today.

How do I calculate the number of days between two dates?

In SQLite, subtract Julian day numbers: julianday('2024-03-10') - julianday('2024-03-01') gives 9. In PostgreSQL, subtracting two DATE values gives the number of days directly, and MySQL has DATEDIFF(later, earlier).

How do I group results by week?

PostgreSQL has DATE_TRUNC('week', order_date), which gives the Monday that starts each week. SQLite can use strftime('%Y-%W', order_date) for year and week number, and MySQL has YEARWEEK(order_date). Week numbering rules differ between functions, so check which day each one treats as the start of a week.

Should dates ever be stored as text?

Only in the ISO format, YYYY-MM-DD, which sorts and compares correctly as text and is how SQLite usually stores dates. Never store dates in local formats such as 05/07/2024: they sort wrongly, and it's unclear whether that's 5 July or 7 May. Prefer a real DATE or TIMESTAMP type where the database has one.

Should I use DATETIME or TIMESTAMP in MySQL?

TIMESTAMP converts values to UTC for storage and back to the session's time zone when read, and its range ends in 2038. DATETIME stores the value exactly as given, without time zone conversion, and covers a much wider range. Many teams use DATETIME with values always written in UTC.

SQL Dates Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the SQLite query so it groups orders by year and month.

SELECT strftime('', order_date) AS month, COUNT(*)
FROM orders
GROUP BY month;

Multiple choiceeasy

Which date format should you use in SQL?

Predict the outputmedium

What does SELECT date('2024-07-30', '+5 days'); return in SQLite?

Multiple choicemedium

Which WHERE clause returns every order placed in July 2024 from a timestamp column?

Multiple choicehard

How do you get the number of days between two dates in MySQL?

SQL Dates Quiz

5 questions to check what you learned.

Which function gives today's date in SQLite?
- date('now')
- NOW()
- TODAY()
- CURDATE()
Why should monthly reports group by year and month together?
- It's faster
- Grouping by month alone would combine the same month from different years
- Databases require it
- Months can't be grouped
Why do dates in YYYY-MM-DD format sort correctly even as text?
- Databases sort dates specially
- They contain hyphens
- They're shorter
- The largest unit comes first, so alphabetical order matches time order
In SQLite, what does strftime('%w', '2024-07-07') return for a Sunday?
- 7
- 1
- 0
- Sun
Why store timestamps in UTC?
- It avoids confusion from time zones and daylight-saving changes; convert when displaying
- UTC uses less storage
- Local time can't be stored
- SQL only supports UTC

Date types and the current date

Filtering date ranges

Extracting parts and grouping by month

Adding time and measuring gaps

Time zones and timestamps

SQL Dates Syntax

SQL Dates Summary

SQL Dates Examples

Common SQL Dates Mistakes

!Grouping by month number only

!BETWEEN on timestamps

!Dates in a local format

SQL Dates in Real Projects

Dates in reporting

SQL Dates FAQ

SQL Dates Exercises

SQL Dates Quiz

Related SQL topics

Grouping by month number only

BETWEEN on timestamps

Dates in a local format