Adjusting days to months and hours to days is a routine task for any database developer. The need of adjusting hours to days arises while working with intervals. For instance, we may receive an interval that has any number of hours greater than “24”. In such cases, the additional hours must be adjusted to the days. To deal with this, the JUSTIFY_HOURS() function can be used in Postgres.
This blog demonstrates how to adjust the hours to days using the JUSTIFY_HOURS() function.
What Does JUSTIFY_HOURS() Function Do in PostgreSQL?
In PostgreSQL, a built-in DateTime function named JUSTIFY_HOURS() is used to adjust the 24-hour time periods to days. The stated function accepts an interval as an argument and adjusts the given hours to months if the specified hours are greater than 24 hours:
JUSTIFY_HOURS(input_interval);
The return type of the JUSTIFY_HOURS() is an interval.
Example 1: Adjusting Hours to Days
The following snippet demonstrates the basic usage of the JUSTIFY_HOURS() function in Postgres:
SELECT JUSTIFY_HOURS(INTERVAL '84 hours');
The specified hours have been successfully adjusted to respective days. We will consider one more example for a profound understanding of the JUSTIFY_HOURS() function:
SELECT JUSTIFY_HOURS(INTERVAL '2 Days 57 Hours');
The given interval has been adjusted to the days based on the 24-hour time period.
Example 2: Using JUSTIFY_HOURS() on Table’s Data
Use the “SELECT *” query to fetch all data of the “article_record” table:
SELECT * FROM article_record;
The above snippet illustrates that a couple of intervals need to be adjusted to days. To do that, we will apply the JUSTIFY_HOURS() function on the “article_published” column of the selected table:
SELECT *, JUSTIFY_HOURS(article_published) FROM article_record;
The output confirms that the hours have been successfully adjusted to days.
That’s all about the JUSTIFY_HOURS() function.
Conclusion
In PostgreSQL, a built-in DateTime function named JUSTIFY_HOURS() is used to adjust the 24-hour time periods to days. The stated function accepts an interval as an argument and adjusts the given hours to months if the specified hours are greater than 24 hours. Its return type is an interval. This post presented a thorough understanding of adjusting hours to days using Postgres’ JUSTIFY_HOURS() function.
