Dates and Times

Prerequisites: Data types

Dates are essential in real data — and they're where SQL dialects differ most. The ideas are the same everywhere: get today's date, extract parts, and add or subtract intervals.

In SQLite

SQLite stores dates as text in ISO format ('2025-04-22') and provides date functions:

SELECT
  DATE('2025-01-31', '+1 month') AS plus_month,
  DATE('2025-03-01', '-1 day')   AS minus_day,
  STRFTIME('%Y', '2025-04-22')   AS year,
  STRFTIME('%m', '2025-04-22')   AS month,
  JULIANDAY('2025-12-25') - JULIANDAY('2025-04-22') AS days_between;

+------------+------------+------+-------+--------------+
| plus_month | minus_day  | year | month | days_between |
+------------+------------+------+-------+--------------+
| 2025-03-03 | 2025-02-28 | 2025 | 04    |        247.0 |
+------------+------------+------+-------+--------------+

Note that SQLite's +1 month from 31 January overflows into March (there's no 31 February), while PostgreSQL gives 28 February. Date arithmetic at month ends is worth testing in any database.

Grouping by month

A very common task: summarize by month.

SELECT STRFTIME('%Y-%m', ordered_on) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

+---------+--------+
|  month  | orders |
+---------+--------+
| 2025-01 |      1 |
| 2025-02 |      2 |
| 2025-03 |      2 |
| 2025-04 |      3 |
+---------+--------+

(GROUP BY gets its own lesson shortly.)

The same ideas in other databases

-- PostgreSQL
SELECT
  CURRENT_DATE,
  DATE '2025-01-31' + INTERVAL '1 month',
  EXTRACT(YEAR FROM ordered_on),
  DATE_TRUNC('month', ordered_on),
  DATE '2025-12-25' - DATE '2025-04-22'      -- a whole number of days
FROM orders;

-- MySQL
SELECT
  CURDATE(),
  DATE_ADD('2025-01-31', INTERVAL 1 MONTH),
  YEAR(ordered_on),
  DATE_FORMAT(ordered_on, '%Y-%m'),
  DATEDIFF('2025-12-25', '2025-04-22')
FROM orders;

CURRENT_DATE and EXTRACT(part FROM date) are standard SQL; functions for formatting and truncating vary.

Filtering by date ranges

SELECT id, ordered_on
FROM orders
WHERE ordered_on >= '2025-02-01' AND ordered_on < '2025-03-01';

+----+------------+
| id | ordered_on |
+----+------------+
|  2 | 2025-02-14 |
|  3 | 2025-02-20 |
+----+------------+

A half-open range (>= start and < next start) works correctly for both dates and timestamps.

Common mistakes

Storing dates in non-ISO text formats like '22/04/2025'. They don't sort or compare correctly. Always use a real date type, or ISO text in SQLite.
Ignoring time zones. Store timestamps in UTC (PostgreSQL's TIMESTAMPTZ does this for you) and convert for display.