PostgreSQL JUSTIFY_INTERVAL Date/Time Function

PostgreSQL JUSTIFY_INTERVAL Date/Time Function

The PostgreSQL JUSTIFY_INTERVAL function is used to adjust an interval by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments. This function is essential for normalizing intervals to a more human-readable format, particularly when dealing with complex time spans.

Syntax

JUSTIFY_INTERVAL(interval)

The JUSTIFY_INTERVAL function has the following components:

  • interval: The interval to be adjusted.

Example PostgreSQL JUSTIFY_INTERVAL Queries

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

1. Basic JUSTIFY_INTERVAL Example

SELECT JUSTIFY_INTERVAL(interval '1 mon -1 hour') AS justified_interval;

This query adjusts the interval '1 mon -1 hour' by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments, resulting in '29 days 23:00:00'.

2. JUSTIFY_INTERVAL with Days and Hours

SELECT JUSTIFY_INTERVAL(interval '40 days 25 hours') AS justified_interval;

This query adjusts the interval '40 days 25 hours' by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments, resulting in '1 mon 10 days 01:00:00'.

3. JUSTIFY_INTERVAL with Column Values

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

This query retrieves the id, name, and the duration adjusted by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments 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_INTERVAL function to adjust intervals by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments.

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', '1 mon -1 hour'),
       ('Conference', '40 days 25 hours'),
       ('Webinar', '1 year 75 days -5 hours');

Here, we insert data into the events table.

Step 3: Using the JUSTIFY_INTERVAL Function

This step involves using the JUSTIFY_INTERVAL() function to adjust the durations by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments in the events table.

Adjust duration by converting 30-day and 24-hour periods, with sign adjustments:

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

This query adjusts the durations by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments for each row in the events table.

Conclusion

The PostgreSQL JUSTIFY_INTERVAL() function is a fundamental tool for normalizing intervals by converting 30-day time periods to months, 24-hour time periods to days, and making additional sign adjustments. Understanding how to use the JUSTIFY_INTERVAL() function and its syntax is essential for accurate interval adjustments in PostgreSQL databases.