Introduction to the PostgreSQL MAKE_INTERVAL() function

TheMAKE_INTERVAL() function allows you to create aninterval from years, months, weeks, days, hours, minutes, and seconds.

Here’s the syntax of theMAKE_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.

TheMAKE_INTERVAL() function returns a value of interval type.

Besides theMAKE_INTERVAL() function, you can use theINTERVAL literal syntax to create an interval:

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

TheINTERVAL 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, theMAKE_INTERVAL() function offers the flexibility to specify each component separately and is ideal for creating an interval dynamically. For example, you can use theMAKE_INTERVAL() function to create an interval from values stored in a table.

PostgreSQL MAKE_INTERVAL() function examples

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

1) Basic MAKE_INTERVAL() function example

The following example uses theMAKE_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 theMAKE_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 calledtime_data:

CREATE TABLE time_data (    idSERIAL 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 theMAKE_INTERVAL() function to create intervals from the data stored in thetime_data table:

SELECT  MAKE_INTERVAL(    year, month, 0, day, hour, minute, second  ) AS interval_dataFROM  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 theMAKE_INTERVAL() function to construct an interval from the provided components, such as years, months, days, hours, minutes, and seconds.

Last updated onMarch 25, 2024

On this page

• Introduction to the PostgreSQL MAKE_INTERVAL() function
• PostgreSQL MAKE_INTERVAL() function examples
• Summary