JUSTIFY_DAYS

Normalize an INTERVAL so the days component stays below 30 by rolling excess days into months.

Category: datetimeReturns: INTERVALDialect: PostgreSql

Syntax

JUSTIFY_DAYS(interval)

Description

## Overview Normalizes an INTERVAL by converting any excess in the days component into months, using 30 days = 1 month. The hours component is unaffected. Use JUSTIFY_DAYS when you want a human-readable interval breakdown where the days field is between 0 and 29. This function uses a fixed 30-day-per-month approximation and is not calendar-aware: it does not account for actual month lengths. ## Behavior - Returns an INTERVAL. - Returns NULL if the argument is NULL. - Converts every 30 days into 1 month. 60 days becomes 2 months, 35 days becomes 1 month 5 days. - Handles negative intervals symmetrically: -35 days becomes -1 month -5 days. - Does not touch hours/minutes/seconds. For full normalization combine with JUSTIFY_HOURS or call JUSTIFY_INTERVAL. ## Compatibility - Matches the JUSTIFY_DAYS convention: 30-day months, no calendar awareness.

Parameters

Name	Type	Description
`interval`		Specifies the interval whose days component should be normalized to fit in the range 0 through 29.

Examples

-- 35 days becomes 1 month 5 days
SELECT JUSTIFY_DAYS(INTERVAL '35 days') AS result;

-- No change when days are under 30
SELECT JUSTIFY_DAYS(INTERVAL '15 days') AS result;

-- Works on a multi-component interval
SELECT JUSTIFY_DAYS(INTERVAL '2 months 60 days') AS result;

-- Combined with AGE: extract calendar months and remaining days
SELECT JUSTIFY_DAYS(AGE(CURRENT_TIMESTAMP(), hire_date::TIMESTAMP)) AS tenure_normalized
FROM workforce.hr.employees;

-- Hours remain untouched; only days are normalized
SELECT JUSTIFY_DAYS(INTERVAL '40 days 50 hours') AS result;

Pitfalls

JUSTIFY_DAYS uses a fixed 30-day month. The result does not reflect calendar-accurate month boundaries: an interval of 31 days becomes 1 month 1 day, even though it could represent a calendar month ending on any day.
Adding the 'justified' form back to a timestamp may produce a different result than the original interval because calendar-aware TIMESTAMP + INTERVAL arithmetic respects actual month lengths.
JUSTIFY_DAYS leaves hours alone. An interval like '50 hours' is not rolled into days; pair with JUSTIFY_HOURS or use JUSTIFY_INTERVAL for full normalization.