For any database developer, converting or adjusting days into months and hours into days is routine work. To do this, Postgres users use different built-in functions. For instance, the JUSTIFY_HOURS() function adjusts hours to days while the JUSTIFY_DAYS() function adjusts days to months. When it comes to adjusting intervals, it can be done using the JUSTIFY_INTERVAL() function.
This blog demonstrates how to adjust intervals using the JUSTIFY_INTERVAL() function in PostgreSQL.
What Does JUSTIFY_INTERVAL() Function Do in PostgreSQL?
JUSTIFY_INTERVAL() is a built-in DateTime function that utilizes the JUSTIFY_DAYS() and JUSTIFY_HOURS() functions with sign adjustments to adjust the intervals.
JUSTIFY_INTERVAL(input_interval);
The return type of the JUSTIFY_INTERVAL() is an interval.
Example 1: Adjusting Intervals
The following snippet demonstrates the basic usage of the JUSTIFY_INTERVAL() function in Postgres:
SELECT JUSTIFY_INTERVAL(INTERVAL '1 Month 29 Days 84 hours');
The specified hours have been successfully adjusted to days and days have been adjusted to months. One more example is illustrated in the following snippet that explains the use of the JUSTIFY_INTERVAL() function with an additional sign adjustment:
SELECT JUSTIFY_INTERVAL(INTERVAL '1 Month 2 Days -84 hours');
The given interval has been adjusted to the appropriate days and hours.
Example 2: Using JUSTIFY_INTERVAL() on Table’s Data
Use the “SELECT *” query to fetch all data of the “article_record” table:
SELECT * FROM article_record;
Let’s use the JUSTIFY_INTERVAL() function on the selected table to adjust the intervals appropriately:
SELECT *, JUSTIFY_INTERVAL(article_published) FROM article_record;
That’s all about the JUSTIFY_INTERVAL() function.
Conclusion
JUSTIFY_INTERVAL() is a built-in DateTime function that utilizes the JUSTIFY_DAYS() and JUSTIFY_HOURS() functions with sign adjustments to adjust the intervals. Its return type is an interval. This post presented a thorough understanding of adjusting hours and days to appropriate intervals using Postgres’ JUSTIFY_INTERVAL() function.
