PostgreSQL MAKE_INTERVAL Date/Time Function

PostgreSQL MAKE_INTERVAL Date/Time Function

The PostgreSQL MAKE_INTERVAL function is used to create an interval from specified years, months, weeks, days, hours, minutes, and seconds fields, each of which can default to zero. This function is essential for constructing interval values from individual time components, which can be useful in various time-based calculations and data manipulations.

Syntax

MAKE_INTERVAL([years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]])

The MAKE_INTERVAL function has the following components:

  • years: The years component of the interval (default is 0).
  • months: The months component of the interval (default is 0).
  • weeks: The weeks component of the interval (default is 0).
  • days: The days component of the interval (default is 0).
  • hours: The hours component of the interval (default is 0).
  • mins: The minutes component of the interval (default is 0).
  • secs: The seconds component of the interval (default is 0).

Example PostgreSQL MAKE_INTERVAL Queries

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

1. Basic MAKE_INTERVAL Example

SELECT MAKE_INTERVAL(days => 10) AS interval_value;

This query creates an interval from the days field set to 10, resulting in '10 days'.

2. MAKE_INTERVAL with Multiple Components

SELECT MAKE_INTERVAL(years => 1, months => 2, days => 10, hours => 5) AS interval_value;

This query creates an interval from the years, months, days, and hours fields, resulting in '1 year 2 mons 10 days 05:00:00'.

3. MAKE_INTERVAL with Column Values

SELECT id, name, MAKE_INTERVAL(days => vacation_days) AS vacation_interval
FROM employees;

This query retrieves the id, name, and constructs an interval from the vacation days column for each row in the employees table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the MAKE_INTERVAL function to construct intervals from individual components.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data, including their vacation days.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    vacation_days INT
);

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

Step 2: Inserting Data into the Table

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

INSERT INTO employees (name, vacation_days)
VALUES ('Alice', 15),
       ('Bob', 20),
       ('Charlie', 10);

Here, we insert data into the employees table.

Step 3: Using the MAKE_INTERVAL Function

This step involves using the MAKE_INTERVAL() function to construct intervals from the individual components in the employees table.

Construct interval from vacation days column:

SELECT id, name, MAKE_INTERVAL(days => vacation_days) AS vacation_interval
FROM employees;

This query constructs the interval from the vacation days column for each row in the employees table.

Conclusion

The PostgreSQL MAKE_INTERVAL() function is a fundamental tool for constructing interval values from individual time components. Understanding how to use the MAKE_INTERVAL() function and its syntax is essential for accurate interval construction in PostgreSQL databases.