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 ]]]]]]] ) → intervalCode language: SQL (Structured Query Language) (sql)

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'Code language: JavaScript (javascript)

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
  );Code language: SQL (Structured Query Language) (sql)

Output:

          make_interval
---------------------------------
 3 years 6 mons 15 days 04:00:00
(1 row)Code language: SQL (Structured Query Language) (sql)

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();Code language: SQL (Structured Query Language) (sql)

Output:

 make_interval
---------------
 00:00:00
(1 row)Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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 *;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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)Code language: CSS (css)

Summary

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

Was this tutorial helpful ?