Date & Time Functions

Dates power a huge share of real queries: orders this month, customers who signed up last year, days since the last purchase. Date handling is also where databases differ most. This lesson teaches the concepts with SQLite's functions and shows the equivalents in other systems.

How dates are stored

Most databases have dedicated types: DATE, TIME, TIMESTAMP (date and time), and timestamps with time zones. SQLite has no date type; it stores dates as ISO-8601 text such as '2026-03-05' or '2026-03-05 14:30:00', and its date functions understand that format. Because ISO dates sort correctly as text, comparisons and ORDER BY just work.

The current date and time

SELECT date('now') AS today, datetime('now') AS now_utc, time('now') AS time_utc;

The standard keywords CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP work in nearly every database, including SQLite. Times are in UTC unless you ask for local time.

Extracting parts of a date

SELECT order_date,
       strftime('%Y', order_date) AS year,
       strftime('%m', order_date) AS month,
       strftime('%d', order_date) AS day,
       strftime('%w', order_date) AS weekday
FROM orders
WHERE id IN (1, 9, 15);

%w gives the day of the week, 0 = Sunday. Other databases use EXTRACT(YEAR FROM order_date) (standard; PostgreSQL, MySQL, Oracle), or YEAR(order_date)/DATEPART (MySQL, SQL Server).

Grouping by month

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Turning dates into a "year-month" label is the standard way to build monthly reports (grouping is covered in lesson 19).

Date arithmetic

SELECT order_date,
       date(order_date, '+14 days') AS return_deadline,
       date(order_date, '+1 month') AS next_month,
       date(order_date, 'start of month') AS month_start,
       date(order_date, 'start of month', '+1 month', '-1 day') AS month_end
FROM orders
WHERE id IN (2, 11);

SQLite's modifiers are applied left to right. Other databases add intervals: order_date + INTERVAL '14 days' (PostgreSQL), DATE_ADD(order_date, INTERVAL 14 DAY) (MySQL), DATEADD(day, 14, order_date) (SQL Server).

SELECT date('2026-01-31', '+1 month') AS surprise;

Month arithmetic is genuinely ambiguous: there's no 31 February. SQLite overflows into March; PostgreSQL and MySQL clamp to the last day of February. Test end-of-month cases whenever you add months.

Differences between dates

SELECT c.name, c.signup_date,
       julianday('2026-03-31') - julianday(c.signup_date) AS days_as_customer
FROM customers AS c
WHERE c.id IN (1, 9, 10);

julianday turns a date into a day number, so subtracting gives the days between. Elsewhere: date2 - date1 (PostgreSQL), DATEDIFF(date2, date1) (MySQL), DATEDIFF(day, date1, date2) (SQL Server).

This lesson uses a fixed "today" (31 March 2026) so the results don't change. In a real report you'd write julianday('now') or CURRENT_DATE.

Age in whole years

SELECT first_name, hire_date,
       CAST(strftime('%Y', '2026-03-31') AS INTEGER) - CAST(strftime('%Y', hire_date) AS INTEGER)
         - (strftime('%m-%d', '2026-03-31') < strftime('%m-%d', hire_date)) AS years_of_service
FROM employees
WHERE id IN (1, 6, 11);

Subtract the years, then subtract one more if this year's anniversary hasn't arrived yet. PostgreSQL's age() and MySQL's TIMESTAMPDIFF(YEAR, …) do this for you.

Filtering by date ranges

SELECT id, order_date
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

Prefer ranges on the raw column to wrapping it in a function (WHERE strftime('%Y-%m', order_date) = '2026-01'). Both give the same rows, but only the range can use an index (lesson 51).

Time zones

Store timestamps in UTC (or in a "timestamp with time zone" type) and convert to local time for display. Storing local times without the zone makes daylight-saving changes and international users a constant source of bugs.

Try it yourself

For each order, show the weekday name and how many days it is from the order to the end of that month.