PostgreSQL JUSTIFY_DAYS Function

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

PostgreSQL JUSTIFY_DAYS Function Overview #

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

Here’s the syntax of the JUSTIFY_DAYS function:

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

The JUSTIFY_DAYS function accepts a value with the type of INTERVAL you want to adjust.

The JUSTIFY_DAYS function returns the adjusted interval by doing the following:

• Converting days exceeding 30 to months.
• Adding the remaining days.
• Keeping hours, minutes, and seconds the same.

The JUSTIFY_DAYS function returns NULL if the value is NULL.

PostgreSQL JUSTIFY_DAYS() Function Example #

The following example uses the JUSTIFY_DAYS() function to adjust intervals:

SELECT
    JUSTIFY_DAYS(INTERVAL '31 days') interval1,
    JUSTIFY_DAYS(INTERVAL '62 days') interval2,
    JUSTIFY_DAYS(INTERVAL '93 days') interval3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

-[ RECORD 1 ]------------
interval1 | 1 mon 1 day
interval2 | 2 mons 2 days
interval3 | 3 mons 3 daysCode language: plaintext (plaintext)

In this example, the JUSTIFY_DAYS function converts:

• An interval of 31 days to 1 month and 1 day.
• An interval of 62 days to 2 months and 2 days.
• An interval of 93 days to 3 months and 3 days.

Converting Intervals That Include Hours, Minutes, and Seconds #

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

SELECT
    JUSTIFY_DAYS(INTERVAL '10 days 3 hours') interval1,
    JUSTIFY_DAYS(INTERVAL '35 days 15 minutes') interval2,
    JUSTIFY_DAYS(INTERVAL '90 days 29 seconds') interval3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

-[ RECORD 1 ]--------------------
interval1 | 10 days 03:00:00
interval2 | 1 mon 5 days 00:15:00
interval3 | 3 mons 00:00:29Code language: plaintext (plaintext)

Note that the JUSTIFY_DAYS does not change the hours, minutes, and seconds.

Summary #

• Use the JUSTIFY_DAYS function to adjust an interval by converting days that exceed 30 days into months.

Quiz #