There are several date and time functions offered by PostgreSQL and we have discussed some of them previously. The make_interval() Function in PostgreSQL is used to create an interval using the given arguments. In this post, we will elaborate on the functioning of the make_interval() function.

How to Use the make_interval() Function in PostgreSQL?

The make_interval() function takes in 7 parameters i.e. Years, Months, Weeks, Days, Hours, Minutes, and Seconds. The first 6 parameters have a data type of INTEGER while the last parameter i.e. seconds has a data type of DOUBLE PRECISION.

The basic syntax of the function looks like this:

make_interval(Years INT, Months INT, Weeks INT, Days INT, Hours INT, Mins INT, Seconds DOUBLE PRECISION);

The arguments have a default value of 0 and 0.0 in the case of the seconds parameter.

The make_interval() function returns an INTERVAL created by the arguments passed in the function.

Let’s move toward some examples to see how it works:

Examples

Here is how the make_interval() function works:

SELECT make_interval(10, 9, 8, 7, 6, 5, 4.321);

The above query will result in the interval created by the specified arguments. The output of the query will be:

The first argument is created as a year, the second one as months the third argument is always converted into days and added to the fourth argument(days). Similarly, all the arguments are mapped to their respective values.

We can also separate all the arguments using named notation to see how they work:

SELECT
    make_interval(years => 1) AS "years_parameter",
    make_interval(months => 2) AS "months_parameter",
    make_interval(weeks => 3) AS "weeks_parameter",
    make_interval(days => 4) AS "days_parameter",
    make_interval(hours => 5) AS "hours_parameter",
    make_interval(mins => 6) AS "mins_parameter",
    make_interval(secs => 7) AS "secs_parameter";

Now execute the query. The output will look like this:

The named notation simply specifies the arguments by their names followed by ”=>” and then their value. Such as:

SELECT make_interval(Hours => 7);

Changing Output Style

We can also change the format of the output interval style. The style can be any of these:

• Postgres
• postgres_verbose
• sql_standard
• iso_8601

We will see how these all change the format of output one by one.

• Postgres

This is the same format as the above outputs are in. Which looks like this:

SET intervalstyle = 'postgres';
SELECT make_interval(10, 9, 8, 7, 6, 5, 4.321);

• postgres_verbose

This query and the output for the format look like this:

SET intervalstyle = 'postgres_verbose';
SELECT make_interval(10, 9, 8, 7, 6, 5, 4.321);

This format has added more verbosity to the output.

• sql_standard

This query for this format will be:

SET intervalstyle = 'sql_standard';
SELECT make_interval(10, 9, 8, 7, 6, 5, 4.321);

This format looks like this:

• iso_8601

This query and the output for the format look like this:

SET intervalstyle = 'iso_8601';
SELECT make_interval(10, 9, 8, 7, 6, 5, 4.321);

Conclusion

The make_interval() method generates an interval based on the arguments passed into it. All the arguments are of INT data type except the last one i.e. seconds, which is of DOUBLE PRECISION data type, and the default values are 0 and 0.0 respectively. The arguments can be passed using the simple notation or named notation. The output format can also be changed by setting the interval style to that specific style by default it is Postgres style.