Date functions

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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';
Options
• '10'
• '10-31'
• '2022'
• '31'

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.

Items to match
• strftime('%Y', d)
• strftime('%m', d)
• date(d, '+7 days')
• julianday(b) - julianday(a)
Definitions
• The year of d, as text
• The month of d, from '01' to '12'
• One week after d
• The number of days between a and b

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?

Options
• SELECT name FROM codemon WHERE julianday(evolution_date) - julianday(capture_date) <= 85;
• SELECT name FROM codemon WHERE DATEDIFF(evolution_date, capture_date) <= 85;
• SELECT name FROM codemon WHERE evolution_date <= capture_date + 85;
• SELECT name FROM codemon WHERE julianday(evolution_date, capture_date) <= 85;

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

Starter code
-- Write your query below
Back to the course