Lesson 7 of 13 · 20 min
Date arithmetic and durations
By the end of this lesson you can subtract dates and timestamps, convert intervals into numbers of minutes, hours or days, avoid the timestamp-versus-date comparison trap, and count working days when the calendar matters.
Subtraction means different things for different date types, and the result type decides what you can do next:
| expression | result type | example |
|---|---|---|
date - date | integer (days) | DATE '2025-12-31' - DATE '2025-12-24' → 7 |
date + integer | date | promised_date + 2 |
timestamp - timestamp | interval | '1 day 03:15:00' |
timestamp + interval | timestamp | shipped_at + interval '48 hours' |
extract(epoch FROM interval) | numeric (seconds) | 97200 |
An integer of days is easy to average and compare. An interval is precise but awkward in reports: averaging intervals gives values like '1 day 05:47:12.4'. To turn an interval into a plain number, extract its length in seconds with extract(epoch FROM ...) and divide: by 60 for minutes, 3600 for hours, 86400 for days.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons