Number and date functions are the arithmetic layer of SQL: they round, they scale, they measure the distance between two values, and they pull the pieces out of a calendar. Because dates are stored as text in this archive, the date half of the lesson matters most — the same string can be compared, sorted, sliced or measured only once you decide which function should read it.
What do number and date functions actually compute?
Every function in this family takes values in and hands back a single value, per row. That is the whole contract, and it is why they compose: the result of one function is a legal argument to the next. The archive's duration column stores minutes as a REAL, so it can be scaled directly:
SELECT title, duration,
ROUND(duration / 15.0, 2) AS quarter_hours,
ROUND(duration / 15.0) AS whole_quarter_hours
FROM episodes
ORDER BY id
LIMIT 6;
title duration quarter_hours whole_quarter_hours
----------------------------------- -------- -------------- --------------------
Mapping the shore with LiDAR 47.0 3.13 3
The boundary of a map 38.0 2.53 3
Street photography at night 52.0 3.47 3
Developing film in a hotel bathroom 61.0 4.07 4
Composing with a single lens 44.0 2.93 3
Why the horizon curves 29.0 1.93 2
Two things to read here. The 1.0 promotion is what keeps 47 divided by 15 from truncating to 3 before ROUND ever sees it — the same integer-division trap lesson 11 opened with. And the second column shows what ROUND does with no digit argument: it rounds to zero decimal places, so 2.53 and 2.93 both become 3. The number of digits is where you make the precision decision.
What do ABS, modulo and ceiling blocks measure?
Three more numbers, three different questions. ABS answers "how far from the target", ignoring direction — useful when a deviation upward and a deviation downward are equally bad:
SELECT title, downloads, ABS(downloads - 25000) AS gap_from_target
FROM episodes
ORDER BY gap_from_target
LIMIT 4;
title downloads gap_from_target
----------------------------- --------- ---------------
Composing with a single lens 25360 360
The photograph nobody printed 21800 3200
Mapping the shore with LiDAR 28410 3410
The boundary of a map 19205 5795
The modulo operator % answers "what is left over", which makes it a bucketing tool: the last three digits of a download count, or a parity check on a row number. CEIL answers "how many whole units do I need", rounding always up, which is how you price capacity that cannot be fractionally consumed:
SELECT title, downloads,
downloads % 1000 AS last_three,
CEIL(downloads / 500.0) AS blocks_of_500
FROM episodes
ORDER BY id
LIMIT 6;
title downloads last_three blocks_of_500
----------------------------------- --------- ---------- --------------
Mapping the shore with LiDAR 28410 410 57.0
The boundary of a map 19205 205 39.0
Street photography at night 33417 417 67.0
Developing film in a hotel bathroom 41203 203 83.0
Composing with a single lens 25360 360 51.0
Why the horizon curves 17882 882 36.0
Note that CEIL rounds up while FLOOR rounds down, and the difference matters whenever the unit is a physical object. Eleven episodes need twelve seats; nine episodes need nine. Pairing the two with a scale-changing function gives a compact view of the same downloads figure from two directions:
SELECT title,
FLOOR(downloads / 1000.0) AS whole_thousands,
CEIL(downloads / 1000.0) AS rounded_up,
ROUND(SQRT(downloads), 2) AS concentration_index
FROM episodes
ORDER BY id
LIMIT 6;
title whole_thousands rounded_up concentration_index
----------------------------------- --------------- ---------- --------------------
Mapping the shore with LiDAR 28.0 29.0 168.55
The boundary of a map 19.0 20.0 138.58
Street photography at night 33.0 34.0 182.8
Developing film in a hotel bathroom 41.0 42.0 202.99
Composing with a single lens 25.0 26.0 159.25
Why the horizon curves 17.0 18.0 133.72
The square root is the odd one out, and it earns its place by changing the shape of the scale rather than the precision: taking the root of a download count compresses the long tail, so one viral episode cannot dominate an average built on this column. Many analytics teams call this a root-scaled index. The exact function is interchangeable — POW(downloads, 0.5) computes the same value — and the choice of scale belongs to the question being asked, not to the column.
How do you read the pieces out of a date?
In this archive released is an ISO string, YYYY-MM-DD. That layout is deliberately friendly: it sorts chronologically with plain text ordering (lesson 10) and it has fixed-width parts, so a year is the first four characters and a month is characters six and seven. SQLite exposes that through strftime, whose format string says which part you want.
SELECT id, released,
strftime('%Y', released) AS yr,
strftime('%m', released) AS mon,
strftime('%w', released) AS dow_num,
CASE strftime('%w', released)
WHEN '0' THEN 'Sunday'
WHEN '1' THEN 'Monday'
WHEN '2' THEN 'Tuesday'
WHEN '3' THEN 'Wednesday'
WHEN '4' THEN 'Thursday'
WHEN '5' THEN 'Friday'
WHEN '6' THEN 'Saturday'
END AS dow_name
FROM episodes
ORDER BY id
LIMIT 6;
id released yr mon dow_num dow_name
-- ---------- ---- --- ------- ---------
1 2025-03-04 2025 03 2 Tuesday
2 2025-03-11 2025 03 2 Tuesday
3 2025-01-08 2025 01 3 Wednesday
4 2025-01-15 2025 01 3 Wednesday
5 2025-02-05 2025 02 3 Wednesday
6 2025-03-18 2025 03 2 Tuesday
%w returns the weekday as a number where 0 is Sunday, which is a convention rather than a natural ordering — Tuesday sorts before Wednesday, but Saturday sorts before Sunday. The CASE turns the number into a name, and it is worth noticing that SQLite's strftime has no %A specifier for a full weekday name, so the CASE is not optional here. The same trick produces the month key that every later grouping will use:
SELECT id, released, strftime('%Y-%m', released) AS month_key
FROM episodes
ORDER BY released
LIMIT 6;
id released month_key
-- ---------- ---------
7 2024-11-21 2024-11
3 2025-01-08 2025-01
4 2025-01-15 2025-01
5 2025-02-05 2025-02
1 2025-03-04 2025-03
2 2025-03-11 2025-03
The month key sorts correctly as text because the year and the month are both zero-padded, and it groups correctly because every release in a month shares the same seven characters. If the dates were stored day-first, the same expression would produce nonsense, and nothing would warn you — which is why the format is a schema decision, not a data accident.
How do you measure and shift a date?
SQLite stores dates as text but computes with them through julianday, which converts a date into a fractional day number. Subtracting two of them gives an elapsed interval in days; the result is a REAL, so wrap it in CAST(... AS INTEGER) when you want whole days.
SELECT id, released,
CAST(julianday('2026-01-01') - julianday(released) AS INTEGER) AS days_old
FROM episodes
ORDER BY released
LIMIT 6;
id released days_old
-- ---------- --------
7 2024-11-21 406
3 2025-01-08 358
4 2025-01-15 351
5 2025-02-05 330
1 2025-03-04 303
2 2025-03-11 296
Two choices in that query are deliberate. The reference date is a fixed literal rather than 'now', because a query that returns different numbers every time you run it cannot be regression-tested. And the whole days come from an explicit CAST, since the subtraction of two Julian days lands on a fractional value when the input strings carry a time component.
Shifting a date forward is the same machinery with a modifier appended. date(released, '+30 days') walks forward, and named modifiers can also snap to a calendar boundary:
SELECT id, released,
date(released, '+30 days') AS promo_date,
date(released, 'start of month') AS month_start
FROM episodes
ORDER BY id
LIMIT 6;
id released promo_date month_start
-- ---------- ----------- -----------
1 2025-03-04 2025-04-03 2025-03-01
2 2025-03-11 2025-04-10 2025-03-01
3 2025-01-08 2025-02-07 2025-01-01
4 2025-01-15 2025-02-14 2025-01-01
5 2025-02-05 2025-03-07 2025-02-01
6 2025-03-18 2025-04-17 2025-03-01
Every date function here is spelled the SQLite way. Other engines name the same ideas differently: PostgreSQL uses EXTRACT(YEAR FROM released) or date_part('year', released) and date_trunc('month', released); MySQL uses YEAR(released) and DATE_SUB(released, INTERVAL 30 DAY); SQL Server uses DATEPART(month, released) and DATEADD(day, 30, released); Oracle uses ADD_MONTHS(released, 1). The portable habits survive the rename: store dates as ISO text or as a real date type, derive parts explicitly, and never hand-calculate a date difference in application code.
Quick reference
- ROUND takes an optional digit count; the digit count is a business decision about precision, not a technicality.
- ABS measures distance without direction, % returns the remainder, CEIL rounds up and FLOOR rounds down.
- strftime extracts parts of an ISO date; %w gives a weekday number that starts at Sunday.
- julianday differences measure elapsed days, and CAST to INTEGER removes the fraction.
- Prefer a fixed reference date over 'now' so results are reproducible and testable.
Measurement drill: using only the episodes table, write a query that reports, for every episode, its age in days at a fixed reference date, the weekday it was released on, and how many whole 15-minute blocks it fills — then explain in one sentence why each of the three needs a different family of functions.