Working with Dates in SQLite

Mark Evans, VP Operations, is preparing a November review. He wants two numbers: how many orders Cartwheel took in November 2025, and how quickly support resolved tickets. Both are date questions, and SQLite handles dates differently from most databases. Once you see how, it’s simple and predictable.

Dates are text, and that’s fine

SQLite has no separate date type. Cartwheel stores order_date as ISO-8601 text: '2025-11-28 19:55:30'. Because the format runs from largest unit to smallest, alphabetical order is chronological order. Comparing strings compares moments in time, and an index on the column works for date ranges.

Here’s the obvious November query:

SELECT COUNT(*) AS november_orders
FROM orders
WHERE order_date BETWEEN '2025-11-01' AND '2025-11-30';

336 orders. It’s wrong by 11.

'2025-11-30' is shorter than '2025-11-30 19:55:30', and a shorter string that matches the start sorts first. So the upper bound is effectively midnight at the start of 30 November, and everything later that day falls outside. The fix is a half-open range: include the start, exclude the start of the next period.

SELECT COUNT(*) AS november_orders
FROM orders
WHERE order_date >= '2025-11-01'
  AND order_date <  '2025-12-01';

347, the real number. On the busiest days of the Black Friday weekend, those 11 missing orders would be a visible hole in Mark’s chart.

Pulling dates apart

strftime(format, value) returns parts of a date as text:

'%Y-%m' is the workhorse: it turns a timestamp into a month label you can group by in the next lesson. Because the result is text, compare it with text: strftime('%w', order_date) = '0' finds Sunday orders, while = 0 finds nothing.

date() and datetime() normalise a value and accept modifiers, which make period boundaries easy:

SELECT
  date('2025-11-28 19:55:30')                                   AS just_the_date,
  date('2025-11-28', 'start of month')                          AS month_start,
  date('2025-11-28', 'start of month', '+1 month')              AS next_month_start,
  date('2025-11-28', 'start of month', '+1 month', '-1 day')    AS month_end,
  date('2025-11-28', '-6 days')                                 AS week_ago;

Modifiers apply left to right. 'start of month', '+1 month' is exactly the exclusive upper bound a half-open range needs.

Rolling windows and weekdays

“Orders in the last 30 days” is a rolling window, and it hides an off-by-one question: does “last 30 days” as of 31 December include the 31st? Usually yes, which means the window starts on 2 December, 29 days earlier. Build both bounds from the anchor date so the window moves with it:

SELECT COUNT(*) AS orders_last_30_days
FROM orders
WHERE order_date >= date('2025-12-31', '-29 days')
  AND order_date <  date('2025-12-31', '+1 day');

189 orders. The upper bound is the start of the day after the anchor, the same half-open idea as before. Write the window’s first and last day in a comment above the query; whoever reviews it will check exactly that.

Weekday analysis combines strftime('%w', ...) with the CASE expressions from the previous lesson. Mapping ‘0’ and ‘6’ to weekend labels is a common first cut when marketing asks whether weekend campaigns are worth it:

SELECT
  order_id,
  order_date,
  CASE strftime('%w', order_date)
    WHEN '0' THEN 'weekend'
    WHEN '6' THEN 'weekend'
    ELSE 'weekday'
  END AS day_type
FROM orders
ORDER BY order_date DESC
LIMIT 5;

This CASE uses the simple form, CASE value WHEN x THEN ..., which compares one value against a list. It’s handy for mappings like this one; use the searched form, CASE WHEN condition THEN ..., whenever a branch needs a real condition.

Measuring durations

julianday() converts a date to a number of days (with fractions for the time). Subtract two and you have a duration in days; multiply by 24 for hours. Here are December’s urgent tickets with their resolution times:

SELECT
  ticket_id,
  opened_at,
  closed_at,
  ROUND((julianday(closed_at) - julianday(opened_at)) * 24, 1) AS hours_to_close
FROM support_tickets
WHERE priority = 'urgent'
  AND opened_at >= '2025-12-01'
ORDER BY opened_at;

Ticket 691 has no closed_at, so its duration is NULL. That’s correct: it isn’t zero hours, it’s unfinished, and any average you compute later will skip it rather than flatter the team.

You can now filter and measure time. Mark’s other question, average resolution time per priority, needs one more idea: collapsing many rows into one per group. That’s next.