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.