Overview
PostgreSQL, a powerful open-source object-relational database system, has introduced various functions over the years to make working with dates and times easier. One such function is MAKE_INTERVAL, which was added in PostgreSQL 9.4 released in December 2014. This function allows users to create an interval by specifying values for different time units, making the process of constructing intervals more intuitive and flexible.
Purpose
The purpose of the MAKE_INTERVAL function is to create an interval value using specified amounts of time units. It’s particularly useful when you need to perform operations that involve adding or subtracting time intervals to or from timestamps, dates, or other intervals.
Syntax and Parameters
The syntax for using the MAKE_INTERVAL function is:
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)This function accepts parameters providing values for years, months, weeks, days, hours, minutes, and seconds—the function will use zeros as default values if these parameters are omitted.
The returned value is an interval data type representing the amount of time specified by the sum of all provided arguments.
Examples
Example 1: Creating Basic Intervals
This example demonstrates how to use MAKE_INTERVAL to create a simple interval of a specified number of days, and add it to a date.
Query:
SELECT DATE '2023-01-01' + MAKE_INTERVAL(days => 30) AS "DateAfter30Days";
Example 2: Adding Years, Months, and Days
This example illustrates adding a specific number of years, months, and days to a timestamp using the MAKE_INTERVAL function.
Query:
SELECT TIMESTAMP '2023-01-01' + MAKE_INTERVAL(years => 2, months => 3, days => 10) AS "NewTimestamp";
Example 3: Complex Interval Adjustments
Here is how to use MAKE_INTERVAL for more complex adjustments involving weeks, hours, minutes, and seconds.
Query:
SELECT CURRENT_TIMESTAMP + MAKE_INTERVAL(weeks => 5, hours => 36, mins => 720, secs => 30) AS "ComplexAdjustment";
Conclusion
The MAKE_INTERVAL function is a versatile tool in PostgreSQL that simplifies the creation and manipulation of intervals. By allowing users to construct intervals from various time units, MAKE_INTERVAL facilitates clearer and more maintainable SQL queries when dealing with temporal data. Its introduction has enhanced PostgreSQL’s datetime functionality, making it a valuable function for developers and database administrators alike.
