0Pricing
SQL Academy · Lesson

Date Arithmetic and INTERVAL

Add and subtract dates with INTERVAL, compute durations, and build relative date filters like 'last 7 days'.

Date Arithmetic and INTERVAL is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Date and Time Types Recap

Four core temporal types in PostgreSQL:

  • DATE — calendar day, no time
  • TIME — time of day
  • TIMESTAMP — date + time, no zone
  • TIMESTAMPTZ — date + time + zone (recommended for events)

INTERVAL: A Span of Time

INTERVAL is a duration. You add or subtract it from a date/timestamp:

SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '30 days';
SELECT NOW() + INTERVAL '3 months 2 weeks';
SELECT INTERVAL '1 hour' + INTERVAL '15 minutes';

INTERVAL Units

You can use year, month, week, day, hour, minute, second (singular or plural):

SELECT INTERVAL '2 years 6 months';
SELECT INTERVAL '1 week';
SELECT INTERVAL '90 seconds';

Common Relative Date Filters

The bread-and-butter "last N days" queries:

SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

SELECT * FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '1 month';

Adding Days to a DATE

PostgreSQL also allows date + integer as "add N days":

SELECT DATE '2024-01-01' + 30;     -- 2024-01-31
SELECT DATE '2024-01-01' - 1;      -- 2023-12-31

Subtracting Two Timestamps

The difference of two timestamps is an INTERVAL:

SELECT NOW() - '2024-01-01'::TIMESTAMP;
-- → '125 days 14:30:00'

SELECT EXTRACT(EPOCH FROM (NOW() - created_at))/3600 AS hours_ago
FROM events;

AGE: Human-Readable Difference

AGE(end, start) returns a year-month-day INTERVAL:

SELECT AGE(NOW(), birth_date) AS age_interval
FROM users WHERE id = 1;

SELECT EXTRACT(YEAR FROM AGE(birth_date)) AS age_in_years
FROM users;

Adding Months — Careful

Adding a month is calendar-arithmetic. 2024-01-31 + 1 month = 2024-02-29 (clipped):

SELECT DATE '2024-01-31' + INTERVAL '1 month';   -- 2024-02-29
SELECT DATE '2024-03-31' + INTERVAL '1 month';   -- 2024-04-30

CURRENT_DATE vs NOW()

Different return types — pick whichever fits:

SELECT CURRENT_DATE;        -- 2024-03-15 (DATE)
SELECT CURRENT_TIME;        -- 14:30:00+02 (TIMETZ)
SELECT CURRENT_TIMESTAMP;   -- 2024-03-15 14:30:00+02 (TIMESTAMPTZ)
SELECT NOW();               -- alias for CURRENT_TIMESTAMP

Constructing Dates from Parts

Build dates from integer columns:

SELECT MAKE_DATE(year, month, day) FROM birthdays;
SELECT MAKE_TIMESTAMP(2024, 3, 15, 14, 30, 0.0);

Indexes on Date Expressions

An index on created_at handles range queries well. For "events in March", a range filter is faster than month-trunc:

-- Index-friendly:
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

-- Less friendly (works only with expression index):
WHERE date_trunc('month', created_at) = '2024-03-01';

Recap

Date arithmetic in SQL is rich and reliable.

  • INTERVAL for durations
  • Add/subtract directly
  • AGE for human-readable spans
  • Half-open ranges are index-friendliest

Quick Check

Which expression returns events from the last 7 days?

Frequently asked questions

Is the “Date Arithmetic and INTERVAL” lesson free?

Yes — the full text of “Date Arithmetic and INTERVAL” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Date Arithmetic and INTERVAL”?

Add and subtract dates with INTERVAL, compute durations, and build relative date filters like 'last 7 days'. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Date Arithmetic and INTERVAL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Date Arithmetic and INTERVAL
  2. EXTRACT, DATE_TRUNC, AGE
  3. Time Zones: TIMESTAMP vs TIMESTAMPTZ
  4. Common Date Reports (MTD, WoW, YoY)
← Back to SQL Academy