Date functions
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Extracting parts of a date
So far we compared dates as whole YYYY-MM-DD strings. SQLite also has date functions. The most useful one is strftime(format, date), which extracts a part of a date:
SELECT name,
capture_date,
strftime('%Y', capture_date) AS year,
strftime('%m', capture_date) AS month,
strftime('%d', capture_date) AS day
FROM codemon;%Y is the 4-digit year, %m the 2-digit month and %d the 2-digit day. Notice that the results are text: '2022', not the number 2022.
That makes it easy to filter on a single part of the date, whatever the day or month:
SELECT name, capture_date
FROM codemon
WHERE strftime('%Y', capture_date) = '2022';Other databases
strftime is specific to SQLite. PostgreSQL uses EXTRACT(YEAR FROM date) and MySQL also has YEAR(date). The idea is identical, only the name changes.
Exercise 2 — Today, and date arithmetic
The date() function builds a date. With the special value 'now' it returns today's date:
SELECT date('now') AS today;date() also accepts modifiers to move a date forward or backward:
SELECT name,
capture_date,
date(capture_date, '+1 year') AS first_anniversary,
date(capture_date, '+30 days') AS one_month_check
FROM codemon;Modifiers are text like '+7 days', '-1 month' or '+2 years'. Several can be chained: date('now', '-1 year', '+3 days').
Time too
datetime('now') returns the date and the time, and time('now') only the time. 'now' is always in UTC (add the 'localtime' modifier to get your local time).
Exercise 3 — Which month?
What does this query return for Aquachu?
SELECT strftime('%m', capture_date)
FROM codemon
WHERE name = 'Aquachu';Hint : %m extracts the month only, as two-digit text. Aquachu was captured on 2022-10-31.
Exercise 4 — Captured in 2023
Complete the query to find the Codémons captured in 2023, using strftime.
Hint : %Y extracts the year, and strftime returns text, so compare with the text '2023'.
Exercise 5 — Days between two dates
How long did each Codémon take to evolve? You can't subtract two date strings directly. julianday(date) converts a date into a number of days, which you *can* subtract:
SELECT name,
capture_date,
evolution_date,
julianday(evolution_date) - julianday(capture_date) AS days_to_evolve
FROM codemon;Florachu has no evolution date, so the result is NULL: any calculation involving NULL gives NULL.
Combine it with 'now' to count the days since an event:
SELECT name,
CAST(julianday('now') - julianday(capture_date) AS INTEGER) AS days_since_capture
FROM codemon;Why CAST?
julianday('now') includes the current time, so the difference has decimals. CAST(... AS INTEGER) keeps only the whole number of days.
Exercise 6 — Date toolbox
Match each expression with what it computes.
strftime('%Y', d)strftime('%m', d)date(d, '+7 days')julianday(b) - julianday(a)Exercise 7 — Filtering and sorting with functions
Date functions can be used anywhere an expression is allowed — in WHERE, in ORDER BY… Which Codémons were captured in December, whatever the year?
SELECT name, capture_date
FROM codemon
WHERE strftime('%m', capture_date) = '12';And who was captured on the 31st of a month?
SELECT name, capture_date
FROM codemon
WHERE strftime('%d', capture_date) = '31';More formats
%w gives the day of the week (0 = Sunday), %j the day of the year, %H the hour. strftime('%Y-%m', d) returns year and month together, e.g. '2023-06' — handy for monthly reports.
Exercise 8 — Fast evolvers
Which query returns the Codémons that evolved within 85 days of their capture?
Hint : Convert both dates to day numbers with julianday, then subtract. DATEDIFF exists in other databases, not in SQLite.
Exercise 9 — First anniversary
Complete the query so it shows, for each Codémon, the date exactly one year after its capture.
Hint : date() applies modifiers such as '+1 year' to a date.
Exercise 10 — Service record
The Codédex wants a service record for every Codémon.
Write a query that returns, for each Codémon:
1. its name
2. its capture year, named capture_year
3. the number of days between its capture and its last battle, named days_active
-- Write your query below