Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 38 ยท SQL Dialect and Types

Dates, Times and Intervals

Master DuckDB date and time handling: DATE, TIMESTAMP and INTERVAL types, date_trunc, date_part, date_diff, strptime and strftime parsing, series generation and time zones.

Almost every analysis has a time axis: sales per month, signups per week, the gap between two events. DuckDB has proper date and time types, a large function library and parsing tools for the many ways dates are written in real files. Using real types instead of text means sorting, grouping and arithmetic simply work.

SQLite stores dates as text, numbers or Julian days and uses its own date(), strftime() and julianday() functions. The runnable stand-ins use those; the DuckDB forms are read-only blocks. The ideas (truncate, extract, add, subtract) carry over directly.

The types

Type Holds Example literal
DATE A calendar day DATE '2026-03-15'
TIME Time of day TIME '14:30:00'
TIMESTAMP Date and time, no zone TIMESTAMP '2026-03-15 14:30:00'
TIMESTAMPTZ A moment in time, shown in the session zone '2026-03-15 14:30:00+01'::TIMESTAMPTZ
INTERVAL A span of time INTERVAL 3 DAY, INTERVAL '2 hours 30 minutes'

Dates compare, sort and subtract naturally. Subtracting two dates gives a number of days; subtracting two timestamps gives an INTERVAL.

SQL
-- DuckDB (read-only example)
SELECT DATE '2026-03-15' + 30                      AS in_30_days,   -- 2026-04-14
       DATE '2026-03-15' + INTERVAL 1 MONTH        AS next_month,   -- 2026-04-15
       DATE '2026-03-15' - DATE '2026-01-01'       AS days_between, -- 73
       TIMESTAMP '2026-03-15 18:00' - TIMESTAMP '2026-03-15 09:30' AS elapsed;  -- 08:30:00

Pulling pieces out: date_part and extract

SQL
-- DuckDB (read-only example)
SELECT date_part('year', DATE '2026-03-15')   AS year,       -- 2026
       extract(month FROM DATE '2026-03-15')  AS month,      -- 3
       dayname(DATE '2026-03-15')             AS weekday,    -- Sunday
       monthname(DATE '2026-03-15')           AS month_name, -- March
       date_part('dow', DATE '2026-03-15')    AS dow,        -- 0 = Sunday
       date_part('week', DATE '2026-03-15')   AS iso_week;

SQLite offers strftime for the same extraction. This stand-in also groups orders by month, a pattern you will use constantly:

SQL Stand-in: extract parts and group by month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Truncating: date_trunc

date_trunc(part, value) rounds a date or timestamp down to the start of a period. It is the cleanest way to bucket time:

SQL
-- DuckDB (read-only example)
SELECT date_trunc('month', DATE '2026-03-15');          -- 2026-03-01
SELECT date_trunc('week',  DATE '2026-03-15');          -- 2026-03-09 (Monday)
SELECT date_trunc('hour',  TIMESTAMP '2026-03-15 14:37:12');   -- 2026-03-15 14:00:00

SELECT date_trunc('month', ordered_at) AS month, sum(total)
FROM orders GROUP BY ALL ORDER BY ALL;

Unlike strftime('%Y-%m'), the result is still a DATE, so it sorts and charts correctly and you can add intervals to it. time_bucket(INTERVAL '15 minutes', ts) groups timestamps into any width, such as five-minute or 15-minute buckets.

A stand-in for "start of month" in SQLite:

SQL Stand-in: start of month and days since ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Differences and ages: date_diff, date_sub, age

SQL
-- DuckDB (read-only example)
SELECT date_diff('day',   DATE '2026-01-01', DATE '2026-03-15');   -- 73
SELECT date_diff('month', DATE '2026-01-31', DATE '2026-03-01');   -- 2 (counts month boundaries)
SELECT date_sub('month',  DATE '2026-01-31', DATE '2026-03-01');   -- 1 (whole months)
SELECT age(TIMESTAMP '2026-03-15', TIMESTAMP '1990-07-04');        -- 35 years 8 mons 11 days

date_diff counts how many boundaries are crossed: January 31 to March 1 crosses two month boundaries, though barely a month passed. date_sub counts complete units. Choose whichever matches the question you are asking, and document it.

Parsing and formatting: strptime and strftime

Real files contain dates like 15/03/2026, Mar 15, 2026 or 20260315. strptime turns text into a timestamp using a format string and strftime turns a date back into text:

SQL
-- DuckDB (read-only example)
SELECT strptime('15/03/2026', '%d/%m/%Y')              AS parsed;       -- 2026-03-15 00:00:00
SELECT strptime('Mar 15, 2026', '%b %d, %Y')::DATE;
SELECT strftime(DATE '2026-03-15', '%A, %B %d %Y');   -- Sunday, March 15 2026
SELECT try_strptime('not a date', '%Y-%m-%d');        -- NULL

Common format codes: %Y four-digit year, %m month 01-12, %d day, %H hour, %M minute, %S second, %b short month, %B full month, %a/%A weekday names. For CSV files, you can pass dateformat = '%d/%m/%Y' to read_csv so the column arrives as DATE right away (Reading CSV files).

Generating date series

generate_series and range produce rows of dates, which is perfect for filling gaps in a report, since months with no sales otherwise vanish:

SQL
-- DuckDB (read-only example)
SELECT * FROM generate_series(DATE '2026-01-01', DATE '2026-03-01', INTERVAL 1 MONTH);

SELECT d::DATE AS day, coalesce(s.total, 0) AS total
FROM generate_series(DATE '2026-03-01', DATE '2026-03-07', INTERVAL 1 DAY) AS g(d)
LEFT JOIN daily_sales AS s ON s.day = g.d::DATE
ORDER BY day;

Note that generate_series includes the end value and range does not. SQLite uses a recursive query, shown here with the same fill-the-gaps idea:

SQL Stand-in: generate a date series and fill gaps ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Time zones

A TIMESTAMP has no zone; it is "wall clock time". A TIMESTAMPTZ is an instant on the global timeline displayed in your session's time zone. Time zone support comes from the ICU extension, which loads automatically in most builds:

SQL
-- DuckDB (read-only example)
SET TimeZone = 'Europe/Lisbon';
SELECT '2026-07-01 12:00:00+00'::TIMESTAMPTZ;            -- shown as 13:00:00+01
SELECT timezone('America/New_York', TIMESTAMPTZ '2026-07-01 12:00:00+00');
SELECT now(), current_date, current_timestamp;

A reliable approach for data from many places: store instants as TIMESTAMPTZ (or UTC TIMESTAMP), convert to a local zone only for display.

Common mistakes

  • Storing dates as text like '15/03/2026', which sorts wrongly; parse them into DATE.
  • Grouping by the month number alone, which merges the same month from different years. Use date_trunc('month', d).
  • Forgetting the last day when filtering ranges on timestamps. Use ts >= start AND ts < next_day instead of BETWEEN.
  • Mixing TIMESTAMP and TIMESTAMPTZ without thinking about zones.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does date_trunc('month', DATE '2026-03-15') return?

    date_trunc rounds down to the start of the period, here the first day of the month.
  2. 2. What does subtracting two TIMESTAMP values give?

    The difference of two timestamps is an INTERVAL.
  3. 3. Which function converts the text '15/03/2026' into a date value?

    strptime parses text using a format string; strftime formats a date as text.
  4. 4. Why use generate_series with a LEFT JOIN in a report?

    A series supplies every period, and the LEFT JOIN fills in zero for periods with no matching rows.

Summary

  • Use real DATE, TIMESTAMP, TIMESTAMPTZ and INTERVAL types so arithmetic and sorting work.
  • date_trunc buckets time; date_part/extract pulls pieces; date_diff counts boundaries.
  • strptime parses text with a format; strftime formats; try_strptime returns NULL on bad input.
  • generate_series with a LEFT JOIN fills gaps in reports.
  • Store instants as TIMESTAMPTZ or UTC and convert for display.

Next: Pivot, sampling and macros