PostgreSQL JUSTIFY_DAYS Date/Time Function

PostgreSQL JUSTIFY_DAYS Date/Time Function

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

Syntax

JUSTIFY_DAYS(interval)

The JUSTIFY_DAYS function has the following components:

  • interval: The interval to be adjusted.

Example PostgreSQL JUSTIFY_DAYS Queries

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

1. Basic JUSTIFY_DAYS Example

SELECT JUSTIFY_DAYS(interval '1 year 65 days') AS justified_interval;

This query adjusts the interval '1 year 65 days' by converting 30-day time periods to months, resulting in '1 year 2 mons 5 days'.

2. JUSTIFY_DAYS with Only Days

SELECT JUSTIFY_DAYS(interval '65 days') AS justified_interval;

This query adjusts the interval '65 days' by converting 30-day time periods to months, resulting in '2 mons 5 days'.

3. JUSTIFY_DAYS with Column Values

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

This query retrieves the id, name, and the duration adjusted by converting 30-day time periods to months 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_DAYS function to adjust intervals by converting 30-day time periods to months.

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 year 65 days'),
       ('Conference', '45 days'),
       ('Webinar', '2 years 75 days');

Here, we insert data into the events table.

Step 3: Using the JUSTIFY_DAYS Function

This step involves using the JUSTIFY_DAYS() function to adjust the durations by converting 30-day time periods to months in the events table.

Adjust duration by converting 30-day periods to months:

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

This query adjusts the durations by converting 30-day time periods to months for each row in the events table.

Conclusion

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