Date & Time Functions

SQLite has no native DATE or DATETIME storage type — every column, no matter what you name its type, ultimately stores one of SQLite's five storage classes (NULL, INTEGER, REAL, TEXT, BLOB). So instead of a dedicated date type, SQLite gives you a set of date and time functions that work on top of whichever representation you choose to store: ISO-8601 TEXT strings, INTEGER Unix timestamps, or REAL Julian day numbers. The convention almost everyone follows — and the one we'll use for RecipeBox — is ISO-8601 TEXT, because it's human-readable and still sorts correctly as plain text.

Storing Dates as ISO-8601 TEXT

An ISO-8601 string like '2026-08-27 14:30:00' sorts correctly with a plain text comparison (<, >, BETWEEN) because the format goes from largest unit (year) to smallest (seconds). RecipeBox stores a recipe's creation time this way, defaulting to the moment the row is inserted:

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CURRENT_TIMESTAMP (along with CURRENT_DATE and CURRENT_TIME) is a special keyword SQLite understands both as a column default and directly inside a query. It inserts the current UTC date/time as an ISO-8601 string automatically, so every new recipe gets a timestamp with no application code needed.

date(), time() and datetime()

These three functions take a time value and any number of modifiers, and return a formatted string. date() returns just YYYY-MM-DD, time() returns just HH:MM:SS, and datetime() returns both together. The special string 'now' means "the current moment":

SELECT date('now');       -- e.g. '2026-08-27'
SELECT time('now');       -- e.g. '09:15:42'
SELECT datetime('now');   -- e.g. '2026-08-27 09:15:42'
SELECT datetime(r.created_at) FROM recipes r WHERE r.id = 1;

strftime() — Custom Formatting

strftime(format, timestring, modifiers...) is the most flexible date function — it builds any output string you want using format codes. The most common ones are %Y (4-digit year), %m (2-digit month), %d (2-digit day), %H (hour, 24h), %M (minute), and %S (second):

-- Just the year and month a recipe was added, e.g. '2026-08'
SELECT strftime('%Y-%m', created_at) AS added_month
FROM recipes;

-- Custom display format, e.g. '27/08/2026 09:15'
SELECT strftime('%d/%m/%Y %H:%M', created_at) AS display_date
FROM recipes;

Date Arithmetic With Modifiers

Every date function accepts a chain of modifiers that shift the base time — strings like '+7 days', '-3 months', '+1 year', or 'start of month'. This is genuinely one of SQLite's most convenient features, since it avoids manual date math:

-- One week from today
SELECT date('now', '+7 days');

-- The first day of the current month
SELECT date('now', 'start of month');

-- Modifiers chain left to right: three months ago, then back to day 1 of that month
SELECT date('now', '-3 months', 'start of month');

Worked Example: Recipes Added in the Last 30 Days

RecipeBox's "New This Month" feed compares each recipe's created_at against datetime('now', '-30 days') — the current moment shifted 30 days into the past:

SELECT id, title, created_at
FROM recipes
WHERE created_at >= datetime('now', '-30 days')
ORDER BY created_at DESC;

Because created_at is stored as ISO-8601 TEXT, the >= comparison works as a plain string comparison — no date-parsing library required, and the same query runs identically whether RecipeBox is embedded in an Android app, a desktop tool, or a server-side script.