PostgreSQL JUSTIFY_INTERVAL Function

Summary: In this tutorial, you’ll learn how to adjust an interval using the PostgreSQL JUSTIFY_INTERVAL function.

PostgreSQL JUSTIFY_INTERVAL Function Overview #

The JUSTIFY_INTERVAL allows you to adjust an interval by converting hours to days and days to months.

It utilizes the JUSTIFY_DAYS() and JUSTIFY_HOURS() functions with additional sign adjustments.

The following shows the syntax of the JUSTIFY_INTERVAL function:

JUSTIFY_INTERVAL(value)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The JUSTIFY_INTERVAL function accepts one parameter:

• value is the interval you want to adjust.

The JUSTIFY_INTERVAL function returns a new interval with the days and hours adjusted accordingly.

The JUSTIFY_INTERVAL function returns NULL if the input interval is NULL.

Adjusting Days to Months #

The following statement uses the JUSTIFY_INTERVAL function to adjust days to months:

SELECT
  JUSTIFY_INTERVAL(INTERVAL '31 days') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    result
-------------
 1 mon 1 dayCode language: plaintext (plaintext)

Adjusting Hours to Days #

The following example uses the JUSTIFY_INTERVAL function to adjust hours to days:

SELECT
  JUSTIFY_INTERVAL(INTERVAL '25 hours') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      result
----------------
 1 day 01:00:00Code language: plaintext (plaintext)

Adjusting Both Days to Months and Hours to Days #

The following statement uses the JUSTIFY_INTERVAL function to adjust an interval that includes both days and hours:

SELECT
  JUSTIFY_INTERVAL(INTERVAL '31 days 25 hours') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

          result
-----------------------
 1 mon 2 days 01:00:00Code language: plaintext (plaintext)

Adjusting Intervals with Negative Signs #

The following statement uses the JUSTIFY_INTERVAL() function to adjust an interval with negative hours:

SELECT
  JUSTIFY_INTERVAL(INTERVAL '32 days -25 hours') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      result
----------------
 1 mon 23:00:00Code language: plaintext (plaintext)

The function normalizes the 32 days -25 hours interval in the following steps:

1. 32 days - (1 day + 1 hour) = 31 days - 1 hour
2. 31 days = 1 mon + 1 day
3. 1 day - 1 hour = 23:00:00

The final result is an interval 1 mon 23:00:00.

Summary #

• Use the PostgreSQL JUSTIFY_INTERVAL function to adjust an interval by converting hours to days and days to months.

Quiz #