Date Arithmetic

Working with dates and times is one of the most practical skills in SQL. Whether you need to calculate delivery deadlines, find employees hired within a certain range, or group sales by month, date arithmetic is the foundation. PostgreSQL has exceptional date and time support, with intuitive syntax for adding intervals, computing differences, and truncating timestamps.

Getting the Current Date and Time

PostgreSQL provides several functions and constants for the current moment:

-- Current date (no time component)
SELECT CURRENT_DATE;
-- Returns: 2025-04-29  (or whatever today's date is)

-- Current date and time with timezone
SELECT NOW();
-- Returns: 2025-04-29 14:23:45.123456+00

-- Same as NOW()
SELECT CURRENT_TIMESTAMP;

-- Current time only
SELECT CURRENT_TIME;

An important distinction: NOW() returns the same value throughout a single transaction. If you need a value that updates within a transaction, use clock_timestamp() instead.

-- These return the same value within a transaction
BEGIN;
SELECT NOW();           -- 2025-04-29 14:23:45
-- ... some operations ...
SELECT NOW();           -- Still 2025-04-29 14:23:45
SELECT clock_timestamp(); -- 2025-04-29 14:23:47 (actual current time)
COMMIT;

Adding and Subtracting INTERVAL

The INTERVAL data type represents a duration of time. You can add or subtract intervals from dates and timestamps.

-- Add 7 days
SELECT DATE '2025-01-15' + INTERVAL '7 days';
-- Returns: 2025-01-22 00:00:00

-- Subtract 3 months
SELECT DATE '2025-06-15' - INTERVAL '3 months';
-- Returns: 2025-03-15 00:00:00

-- Add hours and minutes
SELECT TIMESTAMP '2025-01-15 10:00:00' + INTERVAL '2 hours 30 minutes';
-- Returns: 2025-01-15 12:30:00

You can combine multiple units in a single interval:

SELECT DATE '2025-01-01' + INTERVAL '1 year 2 months 3 days';
-- Returns: 2026-03-04 00:00:00

Intervals can also be multiplied:

SELECT INTERVAL '1 day' * 7;
-- Returns: 7 days

SELECT INTERVAL '1 hour' * 2.5;
-- Returns: 02:30:00

Practical example -- calculating expected delivery dates for orders:

SELECT
  customer_name,
  order_date,
  order_date + INTERVAL '5 days' AS estimated_delivery,
  order_date + INTERVAL '10 days' AS latest_delivery
FROM orders;

Finding employees who were hired in the last year:

SELECT name, hire_date
FROM employees
WHERE hire_date > CURRENT_DATE - INTERVAL '1 year';

Subtracting Dates

Subtracting one date from another returns an integer representing the number of days between them:

SELECT DATE '2025-03-15' - DATE '2025-01-01';
-- Returns: 73 (days)

Subtracting timestamps returns an INTERVAL:

SELECT TIMESTAMP '2025-03-15 14:00:00' - TIMESTAMP '2025-03-15 10:30:00';
-- Returns: 03:30:00

Example -- how many days ago was each order placed:

SELECT
  customer_name,
  order_date,
  CURRENT_DATE - order_date::date AS days_since_order
FROM orders;

Computing Age with AGE()

The AGE function computes the difference between two dates and returns a human-readable interval in years, months, and days.

Two-argument form (difference between two dates):

SELECT AGE(DATE '2025-06-15', DATE '2020-03-10');
-- Returns: 5 years 3 months 5 days

One-argument form (difference from current date):

SELECT AGE(DATE '2020-03-10');
-- Returns the interval from March 10, 2020 to today

Using AGE with employee data:

SELECT
  name,
  hire_date,
  AGE(CURRENT_DATE, hire_date) AS tenure
FROM employees
ORDER BY tenure DESC;

To extract just the year component from the age:

SELECT
  name,
  hire_date,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed
FROM employees;

Truncating with date_trunc

date_trunc rounds a timestamp down to the specified precision. This is invaluable for grouping records by time periods.

date_trunc(field, source)

Available fields include: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.

SELECT date_trunc('month', TIMESTAMP '2025-03-17 14:30:00');
-- Returns: 2025-03-01 00:00:00

SELECT date_trunc('year', TIMESTAMP '2025-03-17 14:30:00');
-- Returns: 2025-01-01 00:00:00

SELECT date_trunc('hour', TIMESTAMP '2025-03-17 14:30:45');
-- Returns: 2025-03-17 14:00:00

SELECT date_trunc('quarter', TIMESTAMP '2025-08-17 14:30:00');
-- Returns: 2025-07-01 00:00:00

The most common use case is grouping and counting by time period:

-- Count orders per month
SELECT
  date_trunc('month', order_date) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_price) AS monthly_revenue
FROM orders
GROUP BY date_trunc('month', order_date)
ORDER BY order_month;

Grouping employees by hire year:

SELECT
  date_trunc('year', hire_date) AS hire_year,
  COUNT(*) AS employees_hired
FROM employees
GROUP BY date_trunc('year', hire_date)
ORDER BY hire_year;

Combining Date Arithmetic Techniques

Here is a more involved example that uses several date functions together:

-- Find orders placed in the last 90 days, grouped by week
SELECT
  date_trunc('week', order_date) AS week_start,
  COUNT(*) AS weekly_orders,
  ROUND(AVG(total_price), 2) AS avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY date_trunc('week', order_date)
ORDER BY week_start;

Calculating business metrics:

SELECT
  name,
  hire_date,
  CURRENT_DATE - hire_date::date AS total_days,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS full_years,
  hire_date + INTERVAL '1 year' AS first_anniversary
FROM employees
ORDER BY hire_date;

Try It Yourself

1. Write a query that shows each order's order_date, the date 30 days later (as follow_up_date), and the date 90 days later (as review_date).

2. Find all employees who were hired more than 2 years ago. Display their name, hire date, and how long they have been with the company using AGE().

3. Count the number of orders placed each month using date_trunc. Show the month and the count.

4. Calculate the number of days between each employee's hire date and today. Sort by the most recently hired.

5. Find orders placed in the first quarter (January through March) of any year using date_trunc or EXTRACT.