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.

Getting the Current Date and Time

The most fundamental date operation is asking the database what time it is. SQLite provides three constants and a flexible datetime() function family.

SELECT
  DATE('now')           AS today,
  TIME('now')           AS current_time,
  DATETIME('now')       AS current_datetime,
  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.

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

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,
  ROUND(SUM(amount), 2)      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.

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('now', '+30 days')               AS thirty_days_from_now,
  DATE('now', 'start of month')         AS first_of_this_month,
  DATE('now', '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.

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');

SELECT
  customer,
  started_on,
  expires_on,
  CAST(julianday(expires_on) - julianday(started_on) AS INTEGER) AS duration_days,
  CASE
    WHEN expires_on < DATE('now') THEN 'Expired'
    WHEN DATE('now', '+30 days') >= expires_on THEN 'Expiring Soon'
    ELSE 'Active'
  END AS status
FROM subscriptions
ORDER BY expires_on;

julianday() is the cleanest way to get day differences. Subtracting two Julian day values gives the exact number of days between them. The CASE expression layered on top of a date comparison shows a practical pattern for computing subscription status in a single query.

Try It Yourself

A user_events table tracks when users logged in and what action they performed. Write a query that shows:

• How many events occurred per day of the week (0 = Sunday)
• Only include events from the last 90 days
• Order results from most to least events

CREATE TABLE user_events (
  user_id INTEGER NOT NULL,
  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'),
  (1, 'logout',   '2024-01-10 17:00:00'),
  (3, 'login',    '2024-01-13 09:15:00'),
  (2, 'login',    '2024-01-14 10:00:00'),
  (3, 'purchase', '2024-01-15 14:45:00'),
  (1, 'login',    '2024-01-16 08:30:00'),
  (4, 'login',    '2024-01-17 11:00:00'),
  (2, 'logout',   '2024-01-20 16:00:00'),
  (3, 'login',    '2024-01-21 09:00:00');

-- Your query here:
-- Hint: use strftime('%w', occurred_at) for day of week
-- Hint: use DATE('now', '-90 days') for the cutoff
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;

Try modifying the query to filter only 'login' actions, or to break down events by both day of week and action type.

Key Takeaways

• DATE('now'), TIME('now'), and DATETIME('now') retrieve the current UTC date and time in SQLite
• strftime() extracts and formats any part of a date using format codes like %Y, %m, %d, %H, and %w
• Date arithmetic uses modifier strings like '+30 days', '-1 month', and 'start of month' chained inside DATE() or DATETIME()
• julianday() converts dates to a numeric day count, making it easy to calculate the difference in days between two dates
• 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') applies the server's local offset — fine for display, but avoid storing those values directly.

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.