PostgreSQL JUSTIFY_DAYS() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL JUSTIFY_DAYS() function to adjust 30-day intervals as months.

Introduction to the PostgreSQL JUSTIFY_DAYS() function

The JUSTIFY_DAYS() function allows you to normalize an interval by converting days exceeding 30 days into months and remaining days.

Here’s the basic syntax of the JUSTIFY_DAYS() function:

JUSTIFY_DAYS ( value) → interval

In this syntax:

value is an interval value you want to justify.

The JUSTIFY_DAYS() function returns an adjusted interval with:

Days exceeding 30 converted to months.
The remaining days are kept as days.
Hours, minutes, and seconds remain intact.

If the input interval (value) is NULL, the function returns NULL.

PostgreSQL JUSTIFY_DAYS() function examples

Let’s explore some examples of using the PostgreSQL JUSTIFY_DAYS() function.

1) Basic PostgreSQL JUSTIFY_DAYS() function example

The following statement uses the JUSTIFY_DAYS() function to adjust intervals that are multiples of 30 days:

SELECT JUSTIFY_DAYS(INTERVAL '30 days'),
       JUSTIFY_DAYS(INTERVAL '60 days'),
       JUSTIFY_DAYS(INTERVAL '90 days');

Output:

justify_days | justify_days | justify_days
--------------+--------------+--------------
 1 mon        | 2 mons       | 3 mons
(1 row)

2) Using JUSTIFY_DAYS() function with intervals that are not multiple of 30 days

The following example uses the JUSTIFY_DAYS() function to adjust intervals that are not multiples of 30 days:

SELECT JUSTIFY_DAYS(INTERVAL '15 days'),
       JUSTIFY_DAYS(INTERVAL '45 days'),
       JUSTIFY_DAYS(INTERVAL '75 days');

Output:

justify_days | justify_days  |  justify_days
--------------+---------------+----------------
 15 days      | 1 mon 15 days | 2 mons 15 days
(1 row)

3) Using JUSTIFY_DAYS() function with intervals that include hours

The following example uses the JUSTIFY_DAYS() function to adjust intervals that include hours, minutes, and seconds:

SELECT JUSTIFY_DAYS(INTERVAL '15 days 2 hours'),
       JUSTIFY_DAYS(INTERVAL '55 days 30 minutes'),
       JUSTIFY_DAYS(INTERVAL '75 days 45 seconds');

Output:

justify_days   |      justify_days      |      justify_days
------------------+------------------------+-------------------------
 15 days 02:00:00 | 1 mon 25 days 00:30:00 | 2 mons 15 days 00:00:45
(1 row)

The output indicates that the adjusted intervals have the time parts.

Summary

Use the JUSTIFY_DAYS() function to normalize an interval by converting days exceeding 30 days as months.