PostgreSQL MAKE_INTERVAL() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL MAKE_INTERVAL() function to create an interval from the interval’s components

Introduction to the PostgreSQL MAKE_INTERVAL() function

The MAKE_INTERVAL() function allows you to create an interval from years, months, weeks, days, hours, minutes, and seconds.

Here’s the syntax of the MAKE_INTERVAL() function:

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

In this syntax:

• years is an integer representing the number of years.
• months is an integer representing the number of months.
• weeks is an integer representing the number of weeks.
• days is an integer representing the number of days.
• hours is an integer representing the number of hours.
• mins is an integer representing the number of minutes.
• secs is a double-precision number representing the number of seconds.

All of these parameters are optional and default to zero.

The MAKE_INTERVAL() function returns a value of interval type.

Besides the MAKE_INTERVAL() function, you can use the INTERVAL literal syntax to create an interval:

INTERVAL 'X years Y months Z days W hours V minutes U seconds'

The INTERVAL literal syntax allows you to create an interval by specifying all components in a single string. It is suitable for creating static or predefined intervals.

On the other hand, the MAKE_INTERVAL() function offers the flexibility to specify each component separately and is ideal for creating an interval dynamically. For example, you can use the MAKE_INTERVAL() function to create an interval from values stored in a table.

PostgreSQL MAKE_INTERVAL() function examples

Let’s explore some examples of using the MAKE_INTERVAL() function.

1) Basic MAKE_INTERVAL() function example

The following example uses the MAKE_INTERVAL() function to create an interval that represents 1 year, 2 months, 3 days, and 4 hours:

SELECT
 MAKE_INTERVAL(
 years => 3, months => 6, days => 15, hours => 4
 );

Output:

make_interval
---------------------------------
 3 years 6 mons 15 days 04:00:00
(1 row)

2) Using the MAKE_INTERVAL() function with default values

All of the parameters of the MAKE_INTERVAL() function are optional and default to zero. For example, the following statement creates an interval zero:

SELECT MAKE_INTERVAL();

Output:

make_interval
---------------
 00:00:00
(1 row)

3) Using the MAKE_INTERVAL( ) function with table data

First, create a new table called time_data:

CREATE TABLE time_data (
 id SERIAL PRIMARY KEY,
 year INTEGER,
 month INTEGER,
 day INTEGER,
 hour INTEGER,
 minute INTEGER,
 second INTEGER
);

Second, insert some rows into the time_data table:

INSERT INTO time_data (year, month, day, hour, minute, second)
VALUES
 (1, 3, 25, 10, 0, 0),
 (2, 2, 25, 11, 30, 0),
 (3, 1, 25, 13, 15, 0)
RETURNING *;

Output:

id | year | month | day | hour | minute | second
----+------+-------+-----+------+--------+--------
 1 | 1 | 3 | 25 | 10 | 0 | 0
 2 | 2 | 2 | 25 | 11 | 30 | 0
 3 | 3 | 1 | 25 | 13 | 15 | 0
(3 rows)

Third, use the MAKE_INTERVAL() function to create intervals from the data stored in the time_data table:

SELECT
 MAKE_INTERVAL(
 year, month, 0, day, hour, minute, second
 ) AS interval_data
FROM
 time_data;

Output:

interval_data
---------------------------------
 1 year 3 mons 25 days 10:00:00
 2 years 2 mons 25 days 11:30:00
 3 years 1 mon 25 days 13:15:00
(3 rows)

Summary

• Use the MAKE_INTERVAL() function to construct an interval from the provided components, such as years, months, days, hours, minutes, and seconds.