Date and Time Functions

Dates and times are everywhere in real databases. Orders have timestamps. Users have registration dates. Events have start and end times. Almost every meaningful business query involves time in some way — "how many signups this week?", "which subscriptions expire next month?", "what's the average time between order and delivery?"

SQL provides a rich set of functions to work with temporal data: extracting parts like the year or hour, formatting dates into readable strings, performing arithmetic like adding 30 days, and calculating durations between two moments. Getting comfortable with these functions unlocks a whole category of analytical queries.

Note on dialects: Date functions vary more across databases than almost any other area of SQL. This lesson uses SQLite syntax, which is common in browser-based environments. PostgreSQL, MySQL, and SQL Server each have their own variants — the concepts transfer directly even when the function names differ.

What You'll Learn

By the end of this lesson you will write one query that groups a table of timestamps into months and reports each month's totals in order — the shape every trend report in the world is built from. Alongside it you will learn the habit that keeps such a report trustworthy: pinning a query to a fixed reference date rather than to whatever today happens to be, so that the same data always produces the same answer.

This is the milestone the warehouse's monthly trend stands on. The capstone reports revenue month by month, and every row of it comes from cutting a timestamp down to its year and month and grouping on the result. You arrive here able to aggregate rows into groups (Aggregation and Grouping) and to cut text apart with SUBSTR and friends (String Functions) — which matters more than it sounds, because SQLite has no date type and every date in this lesson is a string.

Getting the Current Date and Time

The most fundamental date operation is asking the database what time it is. SQLite gives you three keyword constants — CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP — and a more flexible family of functions, DATE(), TIME() and DATETIME(), which take a time string such as 'now'.

SELECT
  DATE('now')           AS today,
  TIME('now')           AS time_now,
  DATETIME('now')       AS datetime_now,
  CURRENT_DATE          AS constant_date,
  DATETIME('now', 'localtime') AS local_datetime;

'now' is a special time string that resolves to the current UTC moment. You will see these used constantly in WHERE clauses, default column values, and audit timestamps.

Two things about that output are worth knowing before you rely on any of it. First, this environment's clock runs in UTC and has no local timezone configured, so 'localtime' changes nothing here: the last two columns will show the same moment. On a server with a real timezone the modifier does shift the value, which is precisely why it is unsafe to store — the same query returns different text depending on where it ran. Second, and more important for everything that follows: every one of these values is different on every Run. Anything you compute from 'now' is a moving target, so no example in this lesson that has to be reproducible uses it. They use a fixed reference date instead, and the section on the Try It block explains why that is not a workaround but the actual practice.

Extracting Date Parts with strftime

The workhorse of SQLite date formatting is strftime(). It takes a format string and a date value, and returns a formatted string. You can use it to extract any component of a date.

SELECT
  strftime('%Y', '2024-03-15')        AS year,
  strftime('%m', '2024-03-15')        AS month,
  strftime('%d', '2024-03-15')        AS day,
  strftime('%H', '2024-03-15 14:30:00') AS hour,
  strftime('%W', '2024-03-15')        AS week_of_year,
  strftime('%w', '2024-03-15')        AS day_of_week;

The format codes follow strftime conventions:

• %Y — four-digit year
• %m — month (01–12)
• %d — day of month (01–31)
• %H — hour in 24-hour format
• %w — weekday (0 = Sunday, 6 = Saturday)
• %W — week number of the year

Read the name once more before going on: strftime is string format time. That has a consequence for every comparison you will ever write against one of these values, and the block below is where you work out what it is.

SELECT
strftime('%m', '2024-03-15')       AS m,
strftime('%m', '2024-03-15') = 3   AS eq_number,
strftime('%m', '2024-03-15') = '03' AS eq_text,
typeof(strftime('%m', '2024-03-15')) AS kind;

So: every strftime result is text, always, even when every character in it is a digit. March comes back as the two-character string '03', not the number 3. WHERE strftime('%m', sold_at) = 3 therefore matches nothing at all, silently, while = '03' matches March — and if what you want is a number, ask for one explicitly with CAST(strftime('%m', sold_at) AS INTEGER).

This becomes powerful when applied to a table. Imagine a sales table where you want to group revenue by month:

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount REAL NOT NULL,
  sold_at TEXT NOT NULL
);

INSERT INTO sales (amount, sold_at) VALUES
  (120.00, '2024-01-10 09:15:00'),
  (85.50,  '2024-01-22 14:30:00'),
  (200.00, '2024-02-05 11:00:00'),
  (65.75,  '2024-02-18 16:45:00'),
  (310.00, '2024-03-03 10:20:00'),
  (90.00,  '2024-03-27 13:55:00');

SELECT
  strftime('%Y-%m', sold_at)        AS month,
  COUNT(*)                          AS num_sales,
  printf('%.2f', SUM(amount))       AS total_revenue
FROM sales
GROUP BY strftime('%Y-%m', sold_at)
ORDER BY month;

Grouping by strftime('%Y-%m', sold_at) collapses individual sale rows into monthly buckets — a pattern you will use constantly in reporting queries. Two details in that query are deliberate. The revenue column is formatted with printf('%.2f', ...) rather than ROUND(..., 2), because rounding a float leaves it a float and a float prints without its trailing zero: ROUND would show January's total as 205.5 and March's as 400.0, which is not what a money column should look like. And the ORDER BY month is not optional decoration — a GROUP BY promises you one row per group and promises nothing whatsoever about which order they arrive in, so a trend report without an explicit sort is a trend report that may not be in time order.

Grouping by '%Y-%m' also works because of the format those strings are in. 2024-01 sorts before 2024-02 as text for exactly the same reason it does as a date, which is true of ISO 8601 dates and of almost no other date format. Sort 01/15/2025 and 02/20/2024 as text and you get them in the wrong year order. This is the practical argument for storing dates as YYYY-MM-DD strings in SQLite: text comparison and date comparison agree.

Date Arithmetic

One of the most useful capabilities is calculating dates relative to another date. SQLite handles this with modifier strings passed as additional arguments to DATE(), DATETIME(), or strftime().

SELECT
  DATE('2024-03-15')                    AS original,
  DATE('2024-03-15', '+7 days')         AS one_week_later,
  DATE('2024-03-15', '-1 month')        AS one_month_before,
  DATE('2024-03-15', '+1 year')         AS next_year,
  DATE('2024-03-15', '+30 days')        AS thirty_days_later,
  DATE('2024-03-15', 'start of month')  AS first_of_that_month,
  DATE('2024-03-15', 'start of month', '-1 day') AS last_day_of_prev_month;

Modifiers can be chained. 'start of month', '-1 day' is a clean idiom for finding the last day of the previous month — it snaps to the first of the current month, then steps back one day. Note that every date here is a literal rather than 'now': the point of a demonstration is that you can check the answers, and a query built on 'now' prints something different every time you run it.

Month arithmetic has a sharp edge that day arithmetic does not, and it is worth meeting deliberately rather than in production.

SELECT
DATE('2024-03-31', '-1 month') AS march_31_minus_a_month,
DATE('2024-05-31', '-1 month') AS may_31_minus_a_month;

Calculating the Difference Between Dates

SQLite does not have a dedicated DATEDIFF() function like some other databases, but you can compute the difference in days using julianday(), which converts a date to a continuous decimal day count.

CREATE TABLE subscriptions (
  customer TEXT NOT NULL,
  started_on TEXT NOT NULL,
  expires_on TEXT NOT NULL
);

INSERT INTO subscriptions VALUES
  ('Alice',   '2024-01-01', '2024-12-31'),
  ('Bob',     '2024-02-15', '2024-08-14'),
  ('Carol',   '2024-03-01', '2024-05-31'),
  ('David',   '2023-11-01', '2024-10-31'),
  ('Erin',    '2024-04-01', '2024-06-20');

-- The report is computed as of a fixed reference date, not as of today.
SELECT
  customer,
  started_on,
  expires_on,
  CAST(julianday(expires_on) - julianday(started_on) AS INTEGER) AS duration_days,
  CASE
    WHEN expires_on < '2024-06-01' THEN 'Expired'
    WHEN DATE('2024-06-01', '+30 days') >= expires_on THEN 'Expiring Soon'
    ELSE 'Active'
  END AS status
FROM subscriptions
ORDER BY expires_on;

Two things to take from that query. First, the reference date is a literal, '2024-06-01', rather than DATE('now'). Written with 'now' against this fixed seed data, every one of these subscriptions is long expired and the status column reports Expired five times — a demonstration that demonstrates nothing, and one that decayed silently as the calendar moved past the data. Pinning the comparison to a stated reference date is how a date report stays reproducible, and it is also how you test one: pick a date, work out the answer by hand, and check.

Second, julianday() returns a decimal day count, not an integer. It counts from noon, so two dates with no time part differ by a whole number and print as 91.0, but the moment either value carries a time the difference has a fraction: julianday('2024-12-31 06:00') - julianday('2024-01-01') is 365.25. That is why the query wraps the subtraction in CAST(... AS INTEGER), which truncates toward zero rather than rounding — so a gap of 3.9 days reports as 3. If you want whole days, decide explicitly whether you mean truncation or rounding, and say which.

Building a Monthly Bucket, Step by Step

Bucketing timestamps into months is a two-stage job — cut the month out of each timestamp, then count the rows in each bucket — and it is worth seeing the stages separately once before writing them as one query.

CREATE TABLE visits (id INTEGER PRIMARY KEY, seen_at TEXT NOT NULL);
INSERT INTO visits VALUES (1,'2024-02-03 08:00:00'),(2,'2024-02-19 21:30:00'),(3,'2024-03-04 12:00:00');

Three visits are already loaded, two in February and one in March. CREATE TABLE ... AS SELECT builds and fills a table from a query in one statement.

A Filter That Quietly Matches Nothing

CREATE TABLE user_events (id INTEGER PRIMARY KEY, action TEXT NOT NULL, occurred_at TEXT NOT NULL);
INSERT INTO user_events VALUES
(1,'login','2024-01-08 08:00:00'),(2,'purchase','2024-01-09 12:30:00'),
(3,'logout','2024-01-10 17:00:00'),(4,'login','2024-01-13 09:15:00'),
(5,'login','2024-01-14 10:00:00'),(6,'purchase','2024-01-15 14:45:00'),
(7,'login','2024-01-20 08:30:00'),(8,'login','2024-01-21 11:00:00');
SELECT strftime('%w', occurred_at) AS day_of_week, COUNT(*) AS event_count
FROM user_events
WHERE occurred_at >= DATE('now', '-90 days')
GROUP BY strftime('%w', occurred_at)
ORDER BY event_count DESC, day_of_week;

8 row(s) affected
day_of_week | event_count
-------------------------
0 | 2
1 | 2
6 | 2
2 | 1
3 | 1

Try It Yourself

The build below is the warehouse's monthly trend in miniature: seven orders with timestamps, bucketed into months and totalled.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT NOT NULL, cents INTEGER NOT NULL);
INSERT INTO orders VALUES
(1,'2024-01-05 09:00:00',12000),(2,'2024-01-27 16:20:00',8000),
(3,'2024-02-03 11:45:00',15000),(4,'2024-02-14 08:05:00',5000),(5,'2024-02-28 19:30:00',22000),
(6,'2024-03-09 13:00:00',9000),(7,'2024-03-22 10:10:00',31000);
-- TODO 1: report one row per calendar month, labelled as month in YYYY-MM form.
--         January 2024 is the string 2024-01 and holds two orders.
-- TODO 2: add orders (a count) and revenue_cents (a sum) per month.
--         February totals 15000 + 5000 + 22000 = 42000 cents.
-- TODO 3: add a revenue column showing those cents as a two-decimal string of
--         currency units: 42000 cents must read 420.00, and 40000 must read 400.00.
--         Sort the report oldest month first.
SELECT placed_at, cents FROM orders ORDER BY placed_at;

7 row(s) affected
month | orders | revenue_cents | revenue
----------------------------------------
2024-01 | 2 | 20000 | 200.00
2024-02 | 3 | 42000 | 420.00
2024-03 | 2 | 40000 | 400.00

Once it passes, try two variations and predict each before running. First, change the bucket from '%Y-%m' to '%Y' — predict how many rows the report returns and what the single revenue figure is before you run it. Second, delete the ORDER BY entirely and rerun a few times; predict whether the rows come back in month order, then note that whatever you observe is not a promise — the only thing that makes a trend report chronological is saying so.

CREATE TABLE ev (at TEXT NOT NULL);
INSERT INTO ev VALUES ('2024-01-05 09:00:00'),('2024-01-27 16:20:00'),('2024-02-03 11:45:00');

SELECT strftime('%Y-%m', at) AS m, COUNT(*) AS n
FROM ev GROUP BY strftime('%Y-%m', at) ORDER BY m;

SELECT substr(at, 1, 7) AS m, COUNT(*) AS n
FROM ev GROUP BY substr(at, 1, 7) ORDER BY m;

INSERT INTO ev VALUES ('2024-1-27 16:20:00'),('05/03/2024');
SELECT at, strftime('%Y-%m', at) AS via_strftime, substr(at, 1, 7) AS via_substr
FROM ev WHERE at NOT LIKE '____-__-__ %' ORDER BY at;

Key Takeaways

• DATE('now'), TIME('now') and DATETIME('now') retrieve the current UTC date and time; CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are the keyword constants that do the same
• strftime() extracts and formats any part of a date using format codes like %Y, %m, %d, %H, and %w — and always returns text, so strftime('%m', d) = 3 is false while = '03' is true
• Date arithmetic uses modifier strings like '+30 days', '-1 month', and 'start of month' chained inside DATE() or DATETIME(); month arithmetic normalizes an impossible date forward, so '2024-03-31' minus one month is 2024-03-02
• julianday() converts dates to a decimal day count — whole numbers only when both values have no time part — so cast or round it deliberately when you want whole days
• Anchor any date window a report depends on to a stated reference date rather than to 'now', or the report silently answers a different question each time it runs
• Grouping by strftime('%Y-%m', column) is a standard pattern for monthly aggregation in reports
• Date functions vary across SQL dialects — the concepts are universal but function names and syntax differ between SQLite, PostgreSQL, MySQL, and SQL Server

Pro Tip: Store all timestamps in UTC at the database level and convert to local time only at the application layer. Mixing local timestamps in a database leads to ambiguous data around daylight saving transitions and makes cross-timezone queries unreliable. In SQLite, DATETIME('now', 'localtime') converts to whatever local timezone the machine running the query is configured for — which is exactly why it is unsafe to store, since the same expression produces different text on different servers. On this learning environment there is no local offset configured at all, so 'localtime' returns the UTC value unchanged and you will see the two columns agree.

Next Steps

With dates and times covered, the next lesson addresses one of SQL's most common pitfalls: NULL values. You will learn how NULL behaves in comparisons, arithmetic, and aggregations, and how to handle missing data safely.