psql Date Difference: Syntax, Examples, and Best Practices

Date Difference in psql

psql date difference is the SQL operation to compute the interval between two PostgreSQL date/timestamp values using AGE(), EXTRACT(), or direct subtraction, executed via the psql CLI.

SELECT EXTRACT(YEAR FROM AGE('2024-09-20', '2020-09-20')) AS year_difference;

Syntax

-- Using AGE() for interval
SELECT AGE(date1, date2);

-- Extracting a specific unit
SELECT EXTRACT(YEAR FROM AGE(date1, date2));
SELECT EXTRACT(MONTH FROM AGE(date1, date2));
SELECT EXTRACT(DAY FROM AGE(date1, date2));

-- Direct subtraction for days/weeks (both operands DATE)
SELECT date1::date - date2::date AS days;
SELECT (date1::date - date2::date) / 7 AS weeks;

-- Precise hour/minute/second difference using EPOCH
SELECT EXTRACT(EPOCH FROM (timestamp1 - timestamp2)) / 3600 AS hours;

Tested on PostgreSQL 15 with psql (15.4) on Ubuntu 22.04.

Functions and Options

Usage Examples

1. Years between two dates

psql -d mydb -c "SELECT EXTRACT(YEAR FROM AGE('2024-09-20', '2020-09-20')) AS year_difference;"

Returns 4. AGE() computes the full interval; EXTRACT(YEAR) isolates the year component. Ideal for age or tenure calculations.

2. Days between two dates (simple subtraction)

psql -d mydb -c "SELECT ('2024-11-08'::date - '2024-10-25'::date) AS difference_in_days;"

Returns 14. Fastest method for day-level accuracy. Requires explicit ::date cast.

3. Hours between timestamps

psql -d mydb -c "SELECT EXTRACT(EPOCH FROM ('2024-09-20 18:00:00'::timestamp - '2024-09-20 08:00:00'::timestamp)) / 3600 AS hour_difference;"

Returns 10. EXTRACT(EPOCH) gives total seconds; dividing by 3600 yields hours. Useful for SLA monitoring and runtime calculations.

Frequently Asked Questions