PostgreSQL JUSTIFY_HOURS Date/Time Function

PostgreSQL JUSTIFY_HOURS Date/Time Function

The PostgreSQL JUSTIFY_HOURS function is used to adjust an interval by converting 24-hour time periods to days. This function is essential for normalizing intervals to a more human-readable format, particularly when dealing with larger time spans.

Syntax

JUSTIFY_HOURS(interval)

The JUSTIFY_HOURS function has the following components:

  • interval: The interval to be adjusted.

Example PostgreSQL JUSTIFY_HOURS Queries

Let's look at some examples of PostgreSQL JUSTIFY_HOURS function queries:

1. Basic JUSTIFY_HOURS Example

SELECT JUSTIFY_HOURS(interval '48 hours') AS justified_interval;

This query adjusts the interval '48 hours' by converting 24-hour time periods to days, resulting in '2 days'.

2. JUSTIFY_HOURS with Hours and Minutes

SELECT JUSTIFY_HOURS(interval '1 day 25 hours 30 minutes') AS justified_interval;

This query adjusts the interval '1 day 25 hours 30 minutes' by converting 24-hour time periods to days, resulting in '2 days 1 hour 30 minutes'.

3. JUSTIFY_HOURS with Column Values

SELECT id, name, JUSTIFY_HOURS(duration) AS justified_duration
FROM events;

This query retrieves the id, name, and the duration adjusted by converting 24-hour time periods to days for each row in the events table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the JUSTIFY_HOURS function to adjust intervals by converting 24-hour time periods to days.

Step 1: Creating a Table

This step involves creating a new table named events to store event data, including their durations.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    duration INTERVAL
);

In this example, we create a table named events with columns for id, name, and duration.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the events table.

INSERT INTO events (name, duration)
VALUES ('Meeting', '48 hours'),
       ('Conference', '1 day 25 hours 30 minutes'),
       ('Webinar', '72 hours');

Here, we insert data into the events table.

Step 3: Using the JUSTIFY_HOURS Function

This step involves using the JUSTIFY_HOURS() function to adjust the durations by converting 24-hour time periods to days in the events table.

Adjust duration by converting 24-hour periods to days:

SELECT id, name, JUSTIFY_HOURS(duration) AS justified_duration
FROM events;

This query adjusts the durations by converting 24-hour time periods to days for each row in the events table.

Conclusion

The PostgreSQL JUSTIFY_HOURS() function is a fundamental tool for normalizing intervals by converting 24-hour time periods to days. Understanding how to use the JUSTIFY_HOURS() function and its syntax is essential for accurate interval adjustments in PostgreSQL databases.