Dates and Times

Store dates sanely, group by month, and do arithmetic without off-by-one errors.

• Intermediate
• 14 min read
• 3 objectives

Before this lessonLesson 8: String Functions

SQLite has no dedicated date type: it stores dates as ISO text (YYYY-MM-DD), which sorts and compares correctly as plain strings. Other engines have real date types, but the ISO-8601 habit is worth keeping everywhere.

Today, and the parts of a date

strftime(format, value) pulls out any component. The result is always text, so wrap it in CAST(... AS INTEGER) when you need to do maths with it.

SELECT record_date,
       strftime('%Y', record_date) AS year,
       strftime('%m', record_date) AS month,
       strftime('%d', record_date) AS day,
       strftime('%w', record_date) AS weekday
FROM weather;

record_date | year | month | day | weekday
------------+-----+------+----+--------
2015-01-01  | 2015 | 01    | 01  | 4
2015-01-02  | 2015 | 01    | 02  | 5
2015-01-03  | 2015 | 01    | 03  | 6
2015-01-04  | 2015 | 01    | 04  | 0

Grouping by month

Truncating a date to its month is the most common reporting task there is. strftime('%Y-%m', col) gives a sortable 2019-01 label.

SELECT strftime('%Y-%m', trans_date) AS month,
       country,
       COUNT(*) AS trans_count,
       SUM(amount) AS trans_total
FROM transactions
GROUP BY month, country
ORDER BY month, country;

month   | country | trans_count | trans_total
--------+--------+------------+------------
2018-12 | US      | 2           | 3000
2019-01 | DE      | 2           | 2500
2019-01 | US      | 1           | 2000

Filtering a date range

Compare against ISO strings directly. Prefer a half-open range (>= start, < the day after the end) over BETWEEN when the column might carry a time component — BETWEEN would miss everything after midnight on the last day.

SELECT id, trans_date, amount
FROM transactions
WHERE trans_date >= '2019-01-01' AND trans_date < '2019-02-01'
ORDER BY trans_date;

id  | trans_date | amount
----+-----------+-------
123 | 2019-01-01 | 2000
124 | 2019-01-07 | 2000
125 | 2019-01-22 | 500

Date arithmetic

The date() function shifts a date by a modifier. julianday() turns a date into a number, so subtracting two of them gives a gap in days.

SELECT record_date,
       date(record_date, '+7 day')   AS next_week,
       date(record_date, 'start of month') AS month_start,
       CAST(julianday('2015-01-10') - julianday(record_date) AS INTEGER) AS days_until_10th
FROM weather;

record_date | next_week  | month_start | days_until_10th
------------+-----------+------------+----------------
2015-01-01  | 2015-01-08 | 2015-01-01  | 9
2015-01-02  | 2015-01-09 | 2015-01-01  | 8
2015-01-03  | 2015-01-10 | 2015-01-01  | 7
2015-01-04  | 2015-01-11 | 2015-01-01  | 6

First and last events per group

MIN and MAX work on dates exactly as they do on numbers, which answers most "first login" and "latest activity" questions — LeetCode 511 Game Play Analysis I is one line of it.

SELECT player_id, MIN(event_date) AS first_login, MAX(event_date) AS last_login
FROM activity
GROUP BY player_id;

player_id | first_login | last_login
----------+------------+-----------
1         | 2016-03-01  | 2016-03-02
2         | 2016-03-01  | 2016-03-01
3         | 2016-03-02  | 2018-07-03

Counting a window backwards from a date

SELECT COUNT(DISTINCT player_id) AS active_players
FROM activity
WHERE event_date BETWEEN date('2016-03-02', '-29 day') AND '2016-03-02';

active_players
---------------
3

Key takeaways

• Store dates as ISO YYYY-MM-DD so text comparison equals date comparison.
• strftime('%Y-%m', col) is the month bucket for reports.
• Half-open ranges beat BETWEEN when a time component might exist.
• MIN/MAX on a date column answers "first" and "latest".

-- Write your solution here