JUSTIFY_HOURS, JUSTIFY_DAYS, JUSTIFY_INTERVAL

• JUSTIFY_HOURS turns each 24 hours of the time part into one day.
• JUSTIFY_DAYS turns each 30 days into one month.
• JUSTIFY_INTERVAL does both, then makes all three parts carry the same sign.

​

Syntax

JUSTIFY_HOURS(<interval>)
JUSTIFY_DAYS(<interval>)
JUSTIFY_INTERVAL(<interval>)

​

Parameters

​

Return type

​

Remarks

• A day is always 24 hours and a month is always 30 days, regardless of calendar or daylight saving time. Adding a justified interval to a timestamp can therefore give a different result than adding the original one.
• JUSTIFY_HOURS and JUSTIFY_DAYS also fix the sign of the part they carry into: a positive day count with a negative time part borrows one day, and the other way around. JUSTIFY_INTERVAL applies the same rule across months, days, and time, so 1 mon -01:00:00 becomes 29 days 23:00:00.
• If a carry does not fit the 32-bit day or month count, the function raises interval out of range.
• The three functions are compatible with PostgreSQL’s functions of the same names.

​

Examples

SELECT
  JUSTIFY_HOURS(INTERVAL '50 hours') AS h,
  JUSTIFY_DAYS(INTERVAL '65 days') AS d,
  JUSTIFY_INTERVAL(INTERVAL '1 mon 65 days 50 hours') AS j;

SELECT
  JUSTIFY_HOURS(INTERVAL '1 mon -1 hour') AS h,
  JUSTIFY_INTERVAL(INTERVAL '1 mon -1 hour') AS j;