SQL · Guides · October 30, 2026 · 11 min read

SQL date functions: extract, truncate, add intervals and count days between dates

Four kinds of date functions cover almost every task an analyst gets. EXTRACT pulls one part out of a date (the year, the month, the hour, the day of the week). date_trunc rounds a timestamp down to the start of its day, week or month, which is how you group by period. Intervals (+ INTERVAL '2 hours') move a date forward or back. Subtraction gives you the time between two dates. Learn these four and you can build most time-based reports.

Every example here runs on the airline database from the Nomto SQL course: 33,121 flights from 12 July to 10 September 2026 and 262,788 bookings, with times in Moscow time. The queries use PostgreSQL syntax and run in DuckDB, which powers the Nomto SQL room; every result table is the real output. Where PostgreSQL and DuckDB name a function differently, the guide says so.

DATE, TIMESTAMP and INTERVAL

Three types do the work:

In the airline data, flights.scheduled_departure, flights.actual_departure and bookings.book_date are timestamps. To turn a timestamp into a date, cast it: scheduled_departure::date (or the standard CAST(scheduled_departure AS DATE)). Both work in PostgreSQL and DuckDB.

Daily flight counts for the first three days of August:

SELECT scheduled_departure::date AS day,
       COUNT(*) AS flights
FROM flights
WHERE scheduled_departure >= '2026-08-01'
  AND scheduled_departure <  '2026-08-04'
GROUP BY 1
ORDER BY 1;

Notice the filter: “on or after the start of the first day, before the start of the day after the last”. That works on timestamps without any function on the column, which also lets the database use an index. BETWEEN '2026-08-01' AND '2026-08-03' would stop at midnight on 3 August and silently lose almost a whole day.

EXTRACT: get the year, month, hour or weekday

EXTRACT(part FROM value) returns one component as a number:

SELECT flight_no,
       scheduled_departure,
       EXTRACT(year  FROM scheduled_departure) AS yr,
       EXTRACT(month FROM scheduled_departure) AS mon,
       EXTRACT(day   FROM scheduled_departure) AS d,
       EXTRACT(hour  FROM scheduled_departure) AS hr,
       EXTRACT(dow   FROM scheduled_departure) AS dow
FROM flights
WHERE flight_id = 1;

12 July 2026 is a Sunday, and dow returns 0 for it: in both PostgreSQL and DuckDB, dow runs from 0 (Sunday) to 6 (Saturday). If you want Monday = 1 and Sunday = 7, use isodow. Other useful parts: quarter, week (ISO week number), minute and epoch (seconds since 1970, or the length of an interval in seconds, more on that below).

The typical use is grouping by a part of the date. The busiest departure hours:

SELECT EXTRACT(hour FROM scheduled_departure) AS hour,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY flights DESC
LIMIT 5;

And by day of the week, Monday first:

SELECT EXTRACT(isodow FROM scheduled_departure) AS iso_dow,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1;

Fridays are the quietest day in this schedule, Sundays the busiest. Keep in mind that the period is not a whole number of weeks, so some weekdays appear one more time than others. For a fair comparison, divide by the number of each weekday in the range.

PostgreSQL vs DuckDB. Both support EXTRACT(part FROM x) and the function form date_part('part', x). PostgreSQL 14+ returns numeric from EXTRACT and double precision from date_part; DuckDB returns integers. DuckDB also has shortcuts like year(x), hour(x), dayname(x) and monthname(x) that PostgreSQL does not have.

date_trunc: group by day, week or month

EXTRACT(month ...) returns 7 for July of every year, which is wrong when your data spans more than one year. To group by calendar period, round the timestamp down to the start of the period with date_trunc('unit', value):

SELECT date_trunc('month', book_date) AS month,
       COUNT(*) AS bookings,
       ROUND(SUM(total_amount)) AS revenue
FROM bookings
GROUP BY 1
ORDER BY 1;

June and August are partial months here: bookings start on 17 June and the data stops on 11 August. Always check the first and last date before you compare periods.

Weeks work the same way, and date_trunc('week', ...) starts the week on Monday in both PostgreSQL and DuckDB:

SELECT date_trunc('week', scheduled_departure) AS week,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1
LIMIT 5;

The first week holds only Sunday 12 July, which is why it has 558 flights. Other units: 'day', 'hour', 'quarter', 'year'.

date_trunc on a timestamp returns a timestamp. Add ::date if you want a clean date in the report. Note that a period with no rows simply does not appear; if you need every day or month in the output, including empty ones, build a calendar first, as shown in the date spine guide.

Adding and subtracting intervals

Add or subtract an INTERVAL to move a timestamp:

SELECT flight_no,
       scheduled_departure,
       scheduled_departure + INTERVAL '2 hours' AS plus_2h,
       scheduled_departure - INTERVAL '1 day'   AS minus_1d,
       CAST(scheduled_departure AS DATE) + 7    AS week_later
FROM flights
WHERE flight_id = 1;

Adding a plain integer to a DATE adds days, in both databases. On a timestamp, use an interval.

Month arithmetic needs care, because months have different lengths. Adding a month to 31 January does not overflow into March, it stops at the last day of February:

SELECT DATE '2026-01-31' + INTERVAL '1 month' AS a;

The same idea gives you the last day of a month: start of month, plus one month, minus one day.

SELECT d,
       date_trunc('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS month_end,
       last_day(d) AS ld
FROM (SELECT DATE '2026-02-10' AS d);

last_day exists in DuckDB (and MySQL) but not in PostgreSQL; the date_trunc version works everywhere.

Intervals are also how you write “the last N days” filters. In this dataset the snapshot was taken on 11 August 2026 at 18:00, so use that moment instead of now(): WHERE book_date >= TIMESTAMP '2026-08-11 18:00' - INTERVAL '7 days'. On a live database, now() and current_date work in both PostgreSQL and DuckDB.

Counting days and minutes between two dates

Subtracting one timestamp from another gives an interval. The longest departure delays:

SELECT flight_no,
       scheduled_departure,
       actual_departure,
       actual_departure - scheduled_departure AS delay,
       EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60 AS delay_min
FROM flights
WHERE actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 3;

An interval like 04:37:00 reads nicely but you cannot average it into a KPI or compare it to a number. EXTRACT(epoch FROM interval) turns it into seconds; divide by 60 for minutes or by 3600 for hours. This works the same way in PostgreSQL and DuckDB, so it is the safest habit. Average delay per month:

SELECT date_trunc('month', scheduled_departure)::date AS month,
       COUNT(*) AS departed,
       ROUND(AVG(EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights
WHERE actual_departure IS NOT NULL
GROUP BY 1
ORDER BY 1;

For whole days, subtract two dates. DATE - DATE returns an integer number of days in both databases. How far ahead do passengers book? Each row below is one flight segment on a booking:

SELECT b.book_ref,
       b.book_date,
       f.scheduled_departure,
       f.scheduled_departure::date - b.book_date::date AS days_ahead
FROM bookings b
JOIN tickets t         USING (book_ref)
JOIN ticket_flights tf USING (ticket_no)
JOIN flights f         USING (flight_id)
ORDER BY b.book_ref, f.scheduled_departure
LIMIT 5;

Put the difference into buckets with CASE and you have a booking-window report:

SELECT CASE WHEN days < 7  THEN '0-6 days'
            WHEN days < 14 THEN '7-13 days'
            WHEN days < 30 THEN '14-29 days'
            ELSE '30+ days' END AS booked_ahead,
       COUNT(*) AS segments
FROM (
  SELECT f.scheduled_departure::date - b.book_date::date AS days
  FROM bookings b
  JOIN tickets t         USING (book_ref)
  JOIN ticket_flights tf USING (ticket_no)
  JOIN flights f         USING (flight_id)
) s
GROUP BY 1
ORDER BY MIN(days);

date_diff, DATEDIFF and age

Different databases offer different shortcuts, and this is where copied queries break:

PostgreSQL has no date_diff or DATEDIFF, and DuckDB has no to_char. age returns a calendar-aware interval: age(TIMESTAMP '2026-08-11 18:00', TIMESTAMP '2026-06-17 00:00') gives 1 month 24 days 18:00:00 in DuckDB (PostgreSQL prints 1 mon 24 days 18:00:00).

One trap with DuckDB’s date_diff: it counts how many boundaries are crossed, not how many full units have passed:

SELECT date_diff('day', TIMESTAMP '2026-07-12 23:50', TIMESTAMP '2026-07-13 00:10') AS dd;

Twenty minutes, one “day”. For calendar days (booked on the 10th, flew on the 24th) that is exactly what you want. For elapsed time (was the delay longer than a day?), compute the interval and use EXTRACT(epoch ...).

Common mistakes

• Grouping by EXTRACT(month ...) across years. July 2025 and July 2026 end up in one bucket. Use date_trunc('month', ...).
• BETWEEN on timestamps. The upper bound is midnight at the start of the last day. Use >= start AND < next_start.
• Wrapping the column in a function inside WHERE. WHERE date_trunc('day', scheduled_departure) = '2026-08-01' is correct but forces the database to compute the function on every row. A range on the raw column is equivalent and can use an index.
• Averaging intervals for a KPI. Convert to minutes or hours first with EXTRACT(epoch ...), so the number is easy to read and compare.
• Using now() on a snapshot. Training and historical datasets have their own “today”. Hard-code the snapshot moment.
• Forgetting time zones. All times in this dataset are Moscow time, stored as plain timestamps. In real data, check whether a column is timestamp or timestamptz and which zone the events were logged in before you extract the hour.

Dates usually come up together with grouping and running totals; see GROUP BY vs HAVING and running totals in SQL. Text columns have their own toolkit, covered in SQL string functions.

FAQ

How do I get just the date from a timestamp in SQL?

Cast it: scheduled_departure::date or CAST(scheduled_departure AS DATE). Both work in PostgreSQL and DuckDB. date_trunc('day', ...) also drops the time but keeps the timestamp type.

What is the difference between EXTRACT and date_trunc?

EXTRACT returns one part of the date as a number (7 for July). date_trunc returns a full timestamp rounded down to the start of the period (2026-07-01 00:00:00). Use EXTRACT for seasonality questions (“which hour is busiest?”) and date_trunc for time series (“bookings per month”).

How do I calculate the number of days between two dates in PostgreSQL?

Subtract two dates: end_date - start_date returns an integer. If the columns are timestamps, cast them first (t2::date - t1::date) for calendar days, or use EXTRACT(epoch FROM t2 - t1) / 86400 for elapsed days with fractions.

Does date_trunc(‘week’) start on Monday or Sunday?

On Monday, in PostgreSQL and DuckDB. For Sunday-based weeks, shift the date: date_trunc('week', d + INTERVAL '1 day') - INTERVAL '1 day'.

Is there DATEADD in PostgreSQL?

No. Add an interval instead: order_date + INTERVAL '3 days', or + 3 on a DATE column.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文