JUSTIFY_INTERVAL

Normalize an INTERVAL by rolling excess hours into days and excess days into months.

Category: datetimeReturns: INTERVALDialect: PostgreSql

Syntax

JUSTIFY_INTERVAL(interval)

Description

## Overview Performs full interval normalization: rolls excess hours into days (24 hours = 1 day), and then rolls excess days into months (30 days = 1 month). Combines the effects of JUSTIFY_HOURS and JUSTIFY_DAYS into a single call. Use JUSTIFY_INTERVAL to produce a human-readable interval in canonical 'Y months D days H:MM:SS' form, especially for tenure calculations or duration summaries. ## Behavior - Returns an INTERVAL. - Returns NULL if the argument is NULL. - Applies the cascade: seconds -> minutes -> hours -> days (via 24-hour rule) -> months (via 30-day rule). - Sign is unified: '-50 hours + 2 days' becomes '-2 hours' (not '+2 days -50 hours') after normalization. - Does not touch years (years are months * 12 symbolically). - Same 30-day-per-month, 24-hour-per-day approximations as the component-level justifier functions. ## Compatibility - Matches the JUSTIFY_INTERVAL convention. Equivalent to JUSTIFY_DAYS(JUSTIFY_HOURS(interval)).

Parameters

Name	Type	Description
`interval`		Specifies the interval to fully normalize across months (30-day), days (24-hour), and smaller units.

Examples

-- 50 hours + 35 days => 1 month 6 days 2 hours
SELECT JUSTIFY_INTERVAL(INTERVAL '35 days 50 hours') AS result;

-- 100 hours => 4 days 4 hours
SELECT JUSTIFY_INTERVAL(INTERVAL '100 hours') AS result;

-- Already normalized interval is unchanged
SELECT JUSTIFY_INTERVAL(INTERVAL '1 month 2 days 3 hours') AS result;

-- Normalize a result from AGE for display
SELECT JUSTIFY_INTERVAL(AGE(CURRENT_TIMESTAMP(), hire_date::TIMESTAMP)) AS tenure_display
FROM workforce.hr.employees;

-- Mixed positive and negative normalize into a single-sign result
SELECT JUSTIFY_INTERVAL(INTERVAL '-50 hours 2 days') AS result;

Pitfalls

The 30-day month and 24-hour day approximations are not calendar- or DST-accurate. Adding a justified interval to a TIMESTAMP can land on a different wall-clock than adding the original, particularly across month boundaries or DST transitions.
Mixed-sign inputs are collapsed to a single sign. An interval of '2 days -50 hours' becomes '-2 hours' rather than preserving both components separately.
The returned interval may differ visually from the original even when logically equivalent in total microseconds. Downstream code that compares intervals by string form should compare by epoch or by a normalized representation.