Dates and times stored as text or numbers

Game: chapters I, IV, VI, VII and XI

SQLite has no date type. A date is a piece of text or a number that people agree to read as a date, and once the format is right, ordinary comparisons do almost everything.

Three ways to write a moment

Databases such as PostgreSQL have proper DATE, TIME and TIMESTAMP types, with their own arithmetic. SQLite stores only numbers, text and blobs, so dates arrive in one of a few conventional shapes:

The bakery database (see the bakery database page) uses the first two: orders.day, refund.day and customer.joined are ISO dates, orders.time is a clock time. The schema tells you which shape a column uses; check a few rows too, because the type alone does not say whether a text column holds '2026-03-05' or '5 March 2026'.

Why text order is calendar order

Text is compared character by character from the left, the way a dictionary sorts words. The first character that differs decides, and later characters do not matter. An ISO date puts the largest unit first (year, then month, then day), gives each unit a fixed width, and pads with leading zeros. So the first difference between two dates is always in the largest unit that differs, which is exactly how calendar order works: '2026-03-10' and '2026-03-09' first differ at the ninth character, where 1 is greater than 0, and the tenth of March does come after the ninth.

The same reasoning holds for HH:MM times on a 24-hour clock, and for 'YYYY-MM-DD HH:MM' stamps. It fails as soon as the width varies. Without its leading zero, nine o'clock sorts after half past ten, because 9 is greater than 1:

SELECT CASE WHEN '9:05' < '10:30' THEN 'before' ELSE 'after' END AS without_zero,
       CASE WHEN '09:05' < '10:30' THEN 'before' ELSE 'after' END AS with_zero;

Day-first formats such as '05/03/2026' fail the same way as soon as two dates fall in different months: they sort by day of the month first, so '05/03/2026' comes after '04/06/2026'. If you design a table, store ISO text or a number; if you inherit one in another format, convert it before comparing.

Comparing and filtering ranges

Because the order is right, the usual operators work on ISO text as they do on numbers. Compare with a value written in the same shape and in single quotes: day >= '2026-03-05'. BETWEEN a AND b includes both ends, which suits whole days, and clock times too: here the orders of the fourth and the fifth taken between nine and ten.

SELECT id, day, time
FROM orders
WHERE day BETWEEN '2026-03-04' AND '2026-03-05'
  AND time BETWEEN '09:00' AND '10:00'
ORDER BY day, time;

The order taken at exactly 09:00 on the fourth is in the result: BETWEEN keeps its lower end, and would keep an order at 10:00 just as well.

A range that starts or ends in the middle of a day needs both columns. "Every order from noon on the fifth onwards" is a later day, or the same day at a later time. Parentheses keep the OR from swallowing the rest of the condition. LIMIT 8 keeps the first eight: the fifth's afternoon, then Saturday the seventh, since the shop is closed on Fridays.

SELECT id, day, time
FROM orders
WHERE day > '2026-03-05'
   OR (day = '2026-03-05' AND time >= '12:00')
ORDER BY day, time
LIMIT 8;

Gluing the two columns into one stamp, day || ' ' || time >= '2026-03-05 12:00', gives the same rows and reads well, but the database can no longer use an index on day, which matters on large tables. When the column itself is a full stamp such as '2026-03-05 17:45', prefer a half-open range, stamp >= '2026-03-05' AND stamp < '2026-03-06': BETWEEN '2026-03-05' AND '2026-03-05' would miss every stamp of that day, because even '2026-03-05 00:00' sorts after '2026-03-05'.

Taking a date apart: substr

A fixed format means each part sits at a fixed position, so substr(text, start, length) cuts it out (positions count from 1). In an ISO date the year is characters 1 to 4, the year and month 1 to 7, the day 9 and 10; in a time the hour is 1 and 2, the minutes 4 and 5. Here the first seven characters give the month each customer joined.

SELECT name, joined, substr(joined, 1, 7) AS month
FROM customer
WHERE joined < '2024-05-01'
ORDER BY joined, id;

The filter joined < '2024-05-01' compares two pieces of text, and because both are ISO dates, text order is date order: it keeps everyone who joined before the first of May 2024. The month column can then be grouped on, to count the newcomers of each month (see GROUP BY).

A prefix can also be matched with LIKE: joined LIKE '2024-02-%' keeps February 2024. It works, but a range (joined >= '2024-02-01' AND joined < '2024-03-01') says what you mean and can use an index. Avoid patterns with a wildcard on both sides, such as LIKE '%03%': it matches March, the third of any month, and the year 2003.

From text to number: CAST

substr returns text. As long as you only compare or sort, that is fine, but arithmetic needs numbers: CAST(text AS INTEGER) converts. Turning a clock time into the number of minutes since midnight, for instance, lets you add and subtract times: the hours times 60, plus the minutes.

SELECT id, time,
       CAST(substr(time, 1, 2) AS INTEGER) * 60 + CAST(substr(time, 4, 2) AS INTEGER) AS minutes_after_midnight
FROM orders
WHERE day = '2026-03-02' AND time < '12:00'
ORDER BY time, id;

The first two orders of that morning are 475 - 435 = 40 minutes apart.

The hour alone, CAST(substr(time, 1, 2) AS INTEGER), is a number from 0 to 23, which can be compared, bucketed with a CASE, or grouped on directly; the next page, GROUP BY, explains the grouping used below.

SELECT CAST(substr(time, 1, 2) AS INTEGER) AS hour, COUNT(*) AS orders
FROM orders
WHERE time < '12:00'
GROUP BY hour
ORDER BY hour;

Dates stored as numbers

Some systems store a date as the integer YYYYMMDD: 5 March 2026 is 20260305. Larger units sit in the higher digits, so numeric order is calendar order and comparisons work exactly as with ISO text, without quotes this time: the value is a number, and a number is compared with a number (see Types). Integer division and the remainder take it apart: d / 10000 is the year, d / 100 % 100 the month, d % 100 the day. Here it is applied to a number written in the query, to show the arithmetic; CAST(replace(day, '-', '') AS INTEGER) would turn an ISO text date into such a number.

SELECT 20260305 / 10000 AS year, 20260305 / 100 % 100 AS month, 20260305 % 100 AS day_of_month;

What the number does not support is subtraction. Two dates a day apart can differ by 1, by 70 (31 March to 1 April) or by 8870 (31 December to 1 January), because the digits are a code, not a count of days:

SELECT 20260306 - 20260305 AS same_month, 20260401 - 20260331 AS across_months;

A range such as "the first week of March" is still easy to write (d BETWEEN 20260301 AND 20260307); "seven days after a given date" is not, because the end of a month gets in the way. Real date arithmetic needs the database's own date functions, and those differ from one database to the next (SQLite's date() and julianday(), PostgreSQL's DATE type and intervals). For the comparisons, ranges and parts on this page, plain text and integer operations are enough, and they behave the same everywhere.

Grammar: dates, substr and CAST

Everything on this page is done with comparisons of the date text, substr and CAST, which every database knows. Date functions such as strftime differ from one database to the next and are not standard SQL. A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own.

SELECT strftime('%m', joined) AS month
FROM customer;

Write substr(joined, 6, 2) instead, as in Taking a date apart. The others, with what to write instead:

date and time with a single argument are accepted. PostgreSQL's own LEFT(t, n), RIGHT(t, n) and x::integer are accepted too: the terminal runs them as substr and CAST(x AS INTEGER).

Common mistakes

Comparing a text date with a number, day = 20260305, or a number date with text, d = '20260305': write the value in the column's own type and format. Writing a date in a shape other than the column's, such as '2026-3-5' against '2026-03-05', which compares as text and matches nothing. Times without a leading zero. BETWEEN with a date-only end on a column that also holds the time. Subtracting YYYYMMDD numbers to count days. Doing arithmetic on substr results without CAST. Matching dates with LIKE '%...%', which finds the digits anywhere.

Try it: Early customers and Busy hours, on the exercises page.

In the game: the archives keep their dates and times in the shapes this page describes. Chapter I, The Night of the 17th, Chapter IV, The Cab's Week, and Chapter VI, Follow the Francs, compare dates; Chapter VII, The Quietest Hour, and Chapter XI, The Silence, compare times.