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.