Date Arithmetic and Difference

Learning Focus

Use this lesson to do date/time math in PostgreSQL with interval, understand what date - date vs timestamptz - timestamptz returns, and avoid DST/timezone traps.

Concept Overview

PostgreSQL date/time arithmetic is powerful because it has first-class types:

• date
• timestamp (without time zone)
• timestamptz (with time zone)
• interval

You can add/subtract intervals, compute differences, and bucket time for reporting.

Why is it important?

• Correct windows: last 7 days, month-to-date, rolling retention
• SLA calculations: durations between events
• Analytics: time bucketing and trend lines
• Scheduling logic: deadlines and reminders

Where does it fit?

Date arithmetic appears in:

• reporting queries (daily/weekly/monthly aggregates)
• data retention jobs (delete old rows)
• application logic expressed in SQL (expiry timestamps)

---

Syntax & Rules

Core Syntax

Add/subtract an interval:

SELECT TIMESTAMPTZ '2026-03-05 12:00+00' + INTERVAL '2 hours';
SELECT DATE '2026-03-05' + INTERVAL '7 days';

Compute differences:

SELECT DATE '2026-03-10' - DATE '2026-03-05' AS days_between;
SELECT TIMESTAMPTZ '2026-03-05 13:00+00' - TIMESTAMPTZ '2026-03-05 12:00+00' AS delta;

What Different Operations Return

Expression	Return type	Example
date - date	integer (days)	DATE '2026-03-10' - DATE '2026-03-05' -> 5
timestamp - timestamp	interval	duration
timestamptz - timestamptz	interval	duration (absolute time)
timestamp + interval	timestamp	shifted timestamp
timestamptz + interval	timestamptz	shifted timestamp

Useful Functions

Function	Use	Example
age(a, b)	human-ish difference (years/months/days)	age(ts1, ts2)
date_trunc(unit, ts)	bucket to unit	date_trunc('day', ts)
extract(field from ts)	pull pieces / epoch	extract(epoch from ts1-ts2)
make_interval(...)	build intervals programmatically	make_interval(days => 7)

Key Rules and Considerations

• Prefer timestamptz for real-world event times.
• Be careful with daylight saving time: adding INTERVAL '1 day' and adding INTERVAL '24 hours' are not always equivalent in local time zones.
• age() is great for calendar-like differences, but not always appropriate for precise SLA math.
• Use half-open intervals for ranges: [start, end).

---

Step-by-Step Examples

Example 1: Add and Subtract Intervals (Beginner)

SELECT
  DATE '2026-03-05' + INTERVAL '7 days' AS plus_7d,
  TIMESTAMPTZ '2026-03-05 12:00+00' + INTERVAL '2 hours' AS plus_2h,
  TIMESTAMPTZ '2026-03-05 12:00+00' - INTERVAL '30 minutes' AS minus_30m;

Expected output:

        plus_7d         |        plus_2h         |        minus_30m
------------------------+------------------------+------------------------
 2026-03-12 00:00:00+00 | 2026-03-05 14:00:00+00 | 2026-03-05 11:30:00+00
(1 row)

Explanation:

• Intervals can express minutes/hours/days/months.

---

Example 2: Date Differences Return Days (Beginner)

SELECT
  DATE '2026-03-10' - DATE '2026-03-05' AS days_between;

Expected output:

 days_between
-------------
 5
(1 row)

Explanation:

• For date, subtraction is defined as an integer number of days.

---

Example 3: Timestamp Differences Return an Interval (Intermediate)

SELECT
  TIMESTAMPTZ '2026-03-05 13:15+00' - TIMESTAMPTZ '2026-03-05 12:00+00' AS delta,
  EXTRACT(EPOCH FROM (TIMESTAMPTZ '2026-03-05 13:15+00' - TIMESTAMPTZ '2026-03-05 12:00+00')) AS seconds;

Expected output:

   delta   | seconds
-----------+---------
 01:15:00  | 4500
(1 row)

Explanation:

• timestamptz - timestamptz yields an interval.
• Extracting epoch is convenient for APIs and numeric metrics.

---

Example 4: Calendar-Like Differences With age() (Advanced)

SELECT
  age(DATE '2026-03-05', DATE '2000-01-01') AS calendar_age;

Expected output:

 calendar_age
-------------
 26 years 2 mons 4 days
(1 row)

Explanation:

• age() returns years/months/days, which is often what humans expect.

---

Practical Use Cases

1) Last 7 Days Window

Context: filter recent events.

SELECT *
FROM events
WHERE created_at >= now() - INTERVAL '7 days'
  AND created_at <  now();

2) Expiry Timestamps

Context: set expiration time for tokens.

UPDATE tokens
SET expires_at = now() + INTERVAL '15 minutes'
WHERE token_id = 123;

3) SLA Duration in Minutes

Context: compute response time.

SELECT
  ticket_id,
  EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0 AS minutes_to_first_response
FROM tickets;

4) Time Bucketing

Context: daily totals.

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)
FROM events
GROUP BY 1
ORDER BY 1;

---

Common Mistakes & Troubleshooting

Mistake 1: Using age() for Precise Durations

What happens: age() returns calendar differences; months vary in length.

Fix: use direct subtraction (ts2 - ts1) and optionally extract epoch.

---

Mistake 2: DST Surprises When Using Local Time

What happens: day boundaries and "24 hours" are not always the same around DST.

Fix: store event times as timestamptz and define windows in UTC (or explicitly in a business timezone).

---

Mistake 3: Casting Columns in Predicates

Wrong:

WHERE created_at::date = CURRENT_DATE

What happens: can prevent index usage.

Fix:

WHERE created_at >= CURRENT_DATE
  AND created_at <  CURRENT_DATE + INTERVAL '1 day'

Debugging tips:

1. Check the column type (timestamp vs timestamptz).
2. Use EXPLAIN if a range filter is still slow.
3. Use half-open ranges for time windows.
4. When in doubt, standardize on UTC.

---

Best Practices

• ✅ Use timestamptz for event times; ❌ store global time in timestamp.
• ✅ Use ts2 - ts1 for precise durations; ❌ use age() for SLA math.
• ✅ Use half-open windows [start, end); ❌ use string or ::date equality for large tables.
• ✅ Use date_trunc for bucketing; ❌ bucket by formatting strings.
• ✅ Make timezone assumptions explicit; ❌ rely on server defaults.

---

Hands-On Practice

Exercise 1 (Easy): Add an interval

Task: Add 3 days to DATE '2026-03-05'.

-- Your SQL here

Solution:

SELECT DATE '2026-03-05' + INTERVAL '3 days' AS result;

---

Exercise 2 (Medium): Compute day difference

Task: Compute the number of days between 2026-03-10 and 2026-03-05.

-- Your SQL here

Solution:

SELECT DATE '2026-03-10' - DATE '2026-03-05' AS days_between;

---

Exercise 3 (Advanced): Compute duration in seconds

Task: Compute seconds between 2026-03-05 13:15+00 and 2026-03-05 12:00+00.

-- Your SQL here

Solution:

SELECT EXTRACT(EPOCH FROM (
  TIMESTAMPTZ '2026-03-05 13:15+00' - TIMESTAMPTZ '2026-03-05 12:00+00'
)) AS seconds;

---

Connection to Other Concepts

Concept	Why it matters
Current time functions	now() defines rolling windows
Formatting/parsing	math needs typed date/time values
Indexes	range predicates enable index usage
Window functions	moving averages and running totals often depend on time ordering
Transactions	now() is transaction-scoped

---

Visual Learning Diagram

flowchart LR
  A[timestamptz values] --> B[Subtract\n(ts2 - ts1)]
  B --> C[interval]
  C --> D[extract(epoch)]
  A --> E[Add interval\n(ts + interval)]
  E --> F[new timestamptz]
  A --> G[date_trunc\n(bucket)]
  G --> H[Group + Aggregate]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using age() for precision	inconsistent metrics	use subtraction + epoch
Casting columns in filters	slow queries	use range filters
Ignoring DST/timezones	shifted windows	use timestamptz + explicit zones
Bucketing by strings	slow + messy	use date_trunc
Using closed intervals	double-counting boundaries	use [start, end)

---

Quick Reference

SELECT now() - INTERVAL '7 days'
SELECT DATE '2026-03-10' - DATE '2026-03-05'
SELECT TIMESTAMPTZ '2026-03-05 13:00+00' - TIMESTAMPTZ '2026-03-05 12:00+00'
SELECT date_trunc('day', now())
SELECT EXTRACT(EPOCH FROM (now() - statement_timestamp()))

What's Next

• Previous: Formatting and Parsing Dates - Convert between text and typed date/time values.
• Continue to 12. Views - Learn how to package queries as reusable views.
• Module Overview - Return to Date and Time Functions overview.