In PostgreSQL, the make_interval() function creates an interval from years, months, weeks, days, hours, minutes and seconds fields.

You provide the years, months, weeks, days, hours, minutes and/or seconds fields, and it will return an interval in the interval data type.

Syntax

The function has the following syntax:

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)

Example

Here’s an example to demonstrate.

SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

1 year 2 mons 25 days 05:06:07

In this example, I provided all seven arguments.

This is the traditional mechanism for passing arguments to functions in PostgreSQL, and it’s referred to as “positional notation”.

When using positional notation, all arguments are specified in order.

Named Notation

Here’s an example using named notation.

SELECT make_interval(days => 12);

Result:

12 days

In this example, I use named notation in order to specify just one argument. This saves me from having to enter all arguments, just to specify the number of days.

All arguments default to zero, so there’s no adverse impact by omitting them.

Here’s what happens if I don’t use named notation when specifying just one argument.

SELECT make_interval(12);

Result:

12 years

It assumed I meant 12 years.

The good thing about named notation is that you can provide as many arguments as you need, and no more.

For example:

SELECT make_interval(months => 10, days => 5, mins => 47);

Result:

10 mons 5 days 00:47:00

Legacy Named Notation

Older versions of Postgres used a slightly different syntax for named notation. This syntax uses := instead of =>.

Example:

SELECT make_interval(days := 12);

Result:

12 days

As of Postgres 12, this syntax still works for backward compatibility.

Weeks and Days

With Postgres intervals, weeks are represented as days. So if you specify the weeks argument, the result will be in days.

SELECT make_interval(weeks => 2);

Result:

14 days

Therefore if you also specify the days argument, it will be added to the days produced by the weeks argument.

SELECT make_interval(weeks => 2, days => 3);

Result:

17 days

Interval Output Style

In Postgres, the output format of the interval type can be set to one of the following four interval styles: 

• sql_standard
• postgres
• postgres_verbose 
• iso_8601

You can set this using SET intervalstyle. The default is the postgres format.

The previous examples all use the default format (postgres).

Here’s an example that demonstrates the output of make_interval() when using different output styles.

sql_standard

SET intervalstyle = 'sql_standard';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

+1-2 +25 +5:06:07

postgres

This is the default setting.

SET intervalstyle = 'postgres';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

1 year 2 mons 25 days 05:06:07

postgres_verbose

SET intervalstyle = 'postgres_verbose';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

@ 1 year 2 mons 25 days 5 hours 6 mins 7 secs

iso_8601

SET intervalstyle = 'iso_8601';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

P1Y2M25DT5H6M7S