JUSTIFY_HOURS

Normalize an INTERVAL so the hours component stays below 24 by rolling excess hours into days.

Category: datetimeReturns: INTERVALDialect: PostgreSql

Syntax

JUSTIFY_HOURS(interval)

Description

## Overview Normalizes an INTERVAL by converting any excess in the hours component into days, using 24 hours = 1 day. The months component is unaffected. Use JUSTIFY_HOURS when you want human-readable intervals where the hours field is between 0 and 23. ## Behavior - Returns an INTERVAL. - Returns NULL if the argument is NULL. - Converts every 24 hours into 1 day. 50 hours becomes 2 days 2 hours. - Negative intervals normalized symmetrically. - Minutes and seconds overflow into hours first, then hours overflow into days. JUSTIFY_HOURS(INTERVAL '3600 seconds') becomes 1 hour, not 1 day. - Does not touch months/years. For full normalization combine with JUSTIFY_DAYS or use JUSTIFY_INTERVAL. ## Compatibility - Matches the JUSTIFY_HOURS convention: 24-hour days, no DST awareness.

Parameters

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

Examples

-- 30 hours becomes 1 day 6 hours
SELECT JUSTIFY_HOURS(INTERVAL '30 hours') AS result;

-- No change when hours are under 24
SELECT JUSTIFY_HOURS(INTERVAL '12 hours') AS result;

-- Applied to a multi-component interval
SELECT JUSTIFY_HOURS(INTERVAL '3 days 50 hours') AS result;

-- Handle a large second count by first converting to hours, then justifying
SELECT JUSTIFY_HOURS(INTERVAL '10000 seconds') AS result;

-- Calculate and justify latency ranges
SELECT request_id, JUSTIFY_HOURS(finished_at - started_at) AS latency_normalized
FROM telemetry.web.requests;

Pitfalls

JUSTIFY_HOURS uses a fixed 24-hour day. DST days have 23 or 25 hours in local time, so the 'justified' output does not match wall-clock day arithmetic.
The function only processes the hours slot and below; months and years are passed through unchanged. Use JUSTIFY_INTERVAL for month-level normalization too.
Adding the justified interval back to a TIMESTAMP produces the same instant in UTC, but the local wall-clock result across DST boundaries may differ from adding the original interval because the 24-hour-per-day assumption breaks on DST days.