When you add and subtract intervals in PostgreSQL, the result is often "raw": 36 hours, 90 days, 800 minutes. The JUSTIFY_* family reshapes such an interval into something a human can read, rolling spare hours into days and spare days into months.
Three functions and what they carry
PostgreSQL ships three normalization functions, each responsible for one "place":
JUSTIFY_HOURS(i) takes every 24 hours and turns them into 1 day.JUSTIFY_DAYS(i) takes every 30 days and turns them into 1 month.JUSTIFY_INTERVAL(i) does both carries at once and reconciles the signs.
SELECT JUSTIFY_HOURS(INTERVAL '36 hours') AS h,
JUSTIFY_DAYS(INTERVAL '90 days') AS d,
JUSTIFY_INTERVAL(INTERVAL '1 mon 33 days 27 hours') AS full;
Note that none of these flattens the interval down to seconds. PostgreSQL stores an interval as three independent fields — months, days and microseconds — and JUSTIFY_* only shuffles values between those fields without changing the meaning.
Why bother: order duration
Say we measure how long an order has been in processing. Plain time subtraction yields an interval where everything piles up in hours:
SELECT id,
created_at,
NOW() - created_at AS raw_age
FROM orders
WHERE status = 'processing';
The string 52:30:00 is technically correct but awkward in a report. Wrap the result in JUSTIFY_HOURS:
SELECT id,
JUSTIFY_HOURS(NOW() - created_at) AS readable_age
FROM orders
WHERE status = 'processing';
Now it reads as "2 days and some hours" — exactly what a manager expects to see.
Employee tenure and large day counts
JUSTIFY_DAYS is especially handy when you accumulate days and want a rough estimate in months. Imagine aggregating days per department:
SELECT dept,
JUSTIFY_DAYS(SUM(NOW() - created_at)) AS dept_tenure
FROM employees
GROUP BY dept;
When an interval carries both hours and days at the same time, reach for JUSTIFY_INTERVAL — it cleans up everywhere. A good habit is to normalize a difference before comparing it, so mixed signs do not trip you up:
SELECT name,
JUSTIFY_INTERVAL(salary_review_at - hired_at) AS service
FROM employees
ORDER BY service DESC;
Gotcha: 30 days is not a calendar month
The main trap with JUSTIFY_DAYS and JUSTIFY_INTERVAL is that they treat a month as exactly 30 days. Real months are never like that: February has 28 or 29 days, July has 31.
SELECT JUSTIFY_INTERVAL(INTERVAL '60 days');
Because of this, a normalized interval is fine for reports and human-readable labels but wrong for exact date arithmetic. Keep the distinction in mind:
JUSTIFY_* applies fixed rules: 24 hours = 1 day, 30 days = 1 month.AGE(end, start) looks at the real calendar and yields true months and days.
If you need an honest "age," use AGE. If you only need to display an accumulated duration neatly, use JUSTIFY_*.
Differences in other engines
The JUSTIFY_* functions are PostgreSQL-specific; there is no direct equivalent elsewhere.
- MySQL: there is no first-class
interval type, so differences usually live as seconds or days. You normalize by hand with DIV and MOD, for example seconds DIV 86400 for days.
- ClickHouse: intervals are not normalized automatically either; use
dateDiff('day', ...) and plain numeric math to derive the places you want.
The takeaway: JUSTIFY_* is a presentation convenience that lives in PostgreSQL. In portable code, normalize durations yourself, and remember that "a month equals 30 days" is a simplification, not a calendar truth.
When you add and subtract intervals in PostgreSQL, the result is often "raw": 36 hours, 90 days, 800 minutes. The
JUSTIFY_*family reshapes such an interval into something a human can read, rolling spare hours into days and spare days into months.Three functions and what they carry
PostgreSQL ships three normalization functions, each responsible for one "place":
JUSTIFY_HOURS(i)takes every 24 hours and turns them into 1 day.JUSTIFY_DAYS(i)takes every 30 days and turns them into 1 month.JUSTIFY_INTERVAL(i)does both carries at once and reconciles the signs.SELECT JUSTIFY_HOURS(INTERVAL '36 hours') AS h, -- 1 day 12:00:00 JUSTIFY_DAYS(INTERVAL '90 days') AS d, -- 3 mons JUSTIFY_INTERVAL(INTERVAL '1 mon 33 days 27 hours') AS full; -- full -> 2 mons 4 days 03:00:00Note that none of these flattens the interval down to seconds. PostgreSQL stores an interval as three independent fields — months, days and microseconds — and
JUSTIFY_*only shuffles values between those fields without changing the meaning.Why bother: order duration
Say we measure how long an order has been in processing. Plain time subtraction yields an interval where everything piles up in hours:
SELECT id, created_at, NOW() - created_at AS raw_age FROM orders WHERE status = 'processing'; -- raw_age might be, say, '52:30:00'The string
52:30:00is technically correct but awkward in a report. Wrap the result inJUSTIFY_HOURS:SELECT id, JUSTIFY_HOURS(NOW() - created_at) AS readable_age FROM orders WHERE status = 'processing'; -- readable_age -> 2 days 04:30:00Now it reads as "2 days and some hours" — exactly what a manager expects to see.
Employee tenure and large day counts
JUSTIFY_DAYSis especially handy when you accumulate days and want a rough estimate in months. Imagine aggregating days per department:SELECT dept, JUSTIFY_DAYS(SUM(NOW() - created_at)) AS dept_tenure FROM employees GROUP BY dept;When an interval carries both hours and days at the same time, reach for
JUSTIFY_INTERVAL— it cleans up everywhere. A good habit is to normalize a difference before comparing it, so mixed signs do not trip you up:SELECT name, JUSTIFY_INTERVAL(salary_review_at - hired_at) AS service FROM employees ORDER BY service DESC;Gotcha: 30 days is not a calendar month
The main trap with
JUSTIFY_DAYSandJUSTIFY_INTERVALis that they treat a month as exactly 30 days. Real months are never like that: February has 28 or 29 days, July has 31.SELECT JUSTIFY_INTERVAL(INTERVAL '60 days'); -- 2 mons (not "1 month plus some calendar days")Because of this, a normalized interval is fine for reports and human-readable labels but wrong for exact date arithmetic. Keep the distinction in mind:
JUSTIFY_*applies fixed rules: 24 hours = 1 day, 30 days = 1 month.AGE(end, start)looks at the real calendar and yields true months and days.If you need an honest "age," use
AGE. If you only need to display an accumulated duration neatly, useJUSTIFY_*.Differences in other engines
The
JUSTIFY_*functions are PostgreSQL-specific; there is no direct equivalent elsewhere.intervaltype, so differences usually live as seconds or days. You normalize by hand withDIVandMOD, for exampleseconds DIV 86400for days.dateDiff('day', ...)and plain numeric math to derive the places you want.The takeaway:
JUSTIFY_*is a presentation convenience that lives in PostgreSQL. In portable code, normalize durations yourself, and remember that "a month equals 30 days" is a simplification, not a calendar truth.