In PostgreSQL you can add and subtract dates and timestamps like numbers, but the rules depend on the type: a date does not behave like a timestamp. Knowing the difference saves you from the classic bugs in due-date, tenure, and "how old is this event" calculations.
Adding days to a date
The most common case is adding a plain integer to a date. PostgreSQL treats it as days:
SELECT DATE '2024-03-01' + 7 AS next_week;
SELECT DATE '2024-03-01' - 30 AS month_ago;
This is handy for due dates: an invoice usually has to be paid N days after the order is placed.
SELECT id,
created_at::date AS placed_on,
created_at::date + 14 AS due_date
FROM orders
WHERE status = 'pending';
- Adding an integer to a
date adds days.
- You cannot add an integer to a
timestamp — that is a type error, you need an interval.
- For "a month later" do not write
+ 30: months vary in length, use INTERVAL '1 month'.
Date difference versus timestamp difference
Here is the key gotcha. Subtracting two date values yields an integer day count, while subtracting two timestamp values yields an interval.
SELECT DATE '2024-03-01' - DATE '2024-01-15' AS days;
SELECT TIMESTAMP '2024-03-01 09:00'
- TIMESTAMP '2024-01-15 18:30' AS span;
Because of this, the same query behaves differently depending on the column types. To guarantee whole days, cast both operands to date:
SELECT id,
(NOW()::date - created_at::date) AS days_open
FROM orders
WHERE status = 'pending';
Gotcha: NOW() - created_at over two timestamp values returns an interval like 45 days 14:30:00, and comparing it with > 30 fails with a type error. Either cast to date, or compare against an interval: NOW() - created_at > INTERVAL '30 days'.
Intervals for timestamps
To a timestamp you add an interval, not a number. Intervals are calendar-aware: + INTERVAL '1 month' on January 31 lands on February 28 (or 29), not "30 days later".
SELECT created_at + INTERVAL '7 days' AS reminder_at,
created_at + INTERVAL '1 month' AS renew_at,
created_at + INTERVAL '36 hours' AS grace_until
FROM users;
Intervals add and scale, so a "last six months" window stays compact:
SELECT id, email
FROM users
WHERE created_at >= NOW() - INTERVAL '6 months';
Tenure and age in days versus months
For employee tenure you often want raw days — then subtract date values. For a human-readable age in years and months, use AGE, which normalizes the interval by the calendar.
SELECT name, dept,
NOW()::date - created_at::date AS tenure_days,
AGE(NOW(), created_at) AS tenure_human
FROM employees
ORDER BY tenure_days DESC;
Remember: 365 days is not always "a year." If you need completed years, compute them with AGE and EXTRACT(YEAR ...) instead of dividing days by 365.
Differences in other engines
The syntax diverges noticeably between engines:
- MySQL: subtracting dates does not give you days. Use
DATEDIFF(end, start) for days and DATE_ADD(d, INTERVAL 7 DAY) / DATE_SUB to shift. A bare d + 7 does not do what you expect.
- ClickHouse: use
date + 7 (days) and the functions dateDiff('day', start, end), addDays, addMonths; you always state the unit explicitly.
If the code must be portable, do not rely on date - date: wrap the logic in DATEDIFF/dateDiff and use named intervals.
In PostgreSQL you can add and subtract dates and timestamps like numbers, but the rules depend on the type: a
datedoes not behave like atimestamp. Knowing the difference saves you from the classic bugs in due-date, tenure, and "how old is this event" calculations.Adding days to a date
The most common case is adding a plain integer to a
date. PostgreSQL treats it as days:SELECT DATE '2024-03-01' + 7 AS next_week; -- 2024-03-08 SELECT DATE '2024-03-01' - 30 AS month_ago; -- 2024-01-31This is handy for due dates: an invoice usually has to be paid N days after the order is placed.
SELECT id, created_at::date AS placed_on, created_at::date + 14 AS due_date FROM orders WHERE status = 'pending';dateadds days.timestamp— that is a type error, you need aninterval.+ 30: months vary in length, useINTERVAL '1 month'.Date difference versus timestamp difference
Here is the key gotcha. Subtracting two
datevalues yields an integer day count, while subtracting twotimestampvalues yields aninterval.SELECT DATE '2024-03-01' - DATE '2024-01-15' AS days; -- 46 (integer) SELECT TIMESTAMP '2024-03-01 09:00' - TIMESTAMP '2024-01-15 18:30' AS span; -- 45 days 14:30:00Because of this, the same query behaves differently depending on the column types. To guarantee whole days, cast both operands to
date:SELECT id, (NOW()::date - created_at::date) AS days_open FROM orders WHERE status = 'pending';Intervals for timestamps
To a
timestampyou add aninterval, not a number. Intervals are calendar-aware:+ INTERVAL '1 month'on January 31 lands on February 28 (or 29), not "30 days later".SELECT created_at + INTERVAL '7 days' AS reminder_at, created_at + INTERVAL '1 month' AS renew_at, created_at + INTERVAL '36 hours' AS grace_until FROM users;Intervals add and scale, so a "last six months" window stays compact:
SELECT id, email FROM users WHERE created_at >= NOW() - INTERVAL '6 months';Tenure and age in days versus months
For employee tenure you often want raw days — then subtract
datevalues. For a human-readable age in years and months, useAGE, which normalizes the interval by the calendar.SELECT name, dept, NOW()::date - created_at::date AS tenure_days, AGE(NOW(), created_at) AS tenure_human FROM employees ORDER BY tenure_days DESC;Remember: 365 days is not always "a year." If you need completed years, compute them with
AGEandEXTRACT(YEAR ...)instead of dividing days by 365.Differences in other engines
The syntax diverges noticeably between engines:
DATEDIFF(end, start)for days andDATE_ADD(d, INTERVAL 7 DAY)/DATE_SUBto shift. A bared + 7does not do what you expect.date + 7(days) and the functionsdateDiff('day', start, end),addDays,addMonths; you always state the unit explicitly.If the code must be portable, do not rely on
date - date: wrap the logic inDATEDIFF/dateDiffand use named intervals.