While working with date time values in Postgres, we often encounter a situation where we need to adjust the interval’s values. For instance, we may receive an interval that has any number of days greater than “30”. In such situations, we need to adjust the number of days according to the 30-day time period to represent them as months. To do this, the JUSTIFY_DAYS() function can be used in Postgres.

This blog demonstrates how to adjust the days to months using the JUSTIFY_DAYS() function.

What Does JUSTIFY_DAYS() Function Do in PostgreSQL?

In PostgreSQL, a built-in DateTime function named JUSTIFY_DAYS() is used to adjust the 30-day time periods to months. It accepts an interval and adjusts the given days to months if its day field is greater than 30 days:

JUSTIFY_DAYS(input_interval);

The return type of the JUSTIFY_DAYS() is an interval.

Example 1: Adjusting Days to Months

The following snippet demonstrates the basic usage of the JUSTIFY_DAYS() function in Postgres:

SELECT JUSTIFY_DAYS(INTERVAL '84 days');

The output shows that the days have been successfully adjusted to the months. Let’s consider one more example to understand the working of the JUSTIFY_DAYS() function better:

SELECT JUSTIFY_DAYS(INTERVAL '2 Months 57 days');

The given interval has been adjusted to the months based on the 30-day time period.

Example 2: Using JUSTIFY_DAYS() on Table’s Data

Use the SELECT command with the “*” wildcard to fetch all data of the “article_record” table:

SELECT * FROM article_record;

In the above table, there are some intervals that need to be adjusted to months. So for this purpose, we will execute the JUSTIFY_DAYS() function on the article_published column of the given table:

SELECT * , JUSTIFY_DAYS(article_published)
FROM article_record;

The output demonstrates that the days have been successfully adjusted to months.

That was all about the JUSTIFY_DAYS() function.

Conclusion

In PostgreSQL, a built-in DateTime function named JUSTIFY_DAYS() is used to adjust the 30-day time periods to months. It accepts an interval and adjusts the given days to months if its day field is greater than 30 days. Its return type is an interval. This post presented a thorough understanding of adjusting days to months using Postgres’ JUSTIFY_DAYS() function.