Alternatives to DATEADD() in PostgreSQL

1. the DATEADD() Function in SQL
2. Use the + and - Operators as Alternative to DATEADD() in PostgreSQL
3. Use the INTERVAL Data Type as Alternative to DATEADD() in PostgreSQL

This article discusses the alternatives to the DATEADD() function in PostgreSQL.

the DATEADD() Function in SQL

Suppose you have the manufacture date of a product saved in your database, along with the number of days it takes for it to expire. We can demonstrate this through the following table:

Create table product
(
name varchar(30) not null,
manufacture_date date,
expires_in int,
constraint PK_PRODUCT primary key (name)
);

Now, let us fill this table with sample data of a product:

insert into product values ('Fruit Juice', '2022-08-24', 10);

Output:

From this data, we only know that the product expires in 10 days, but we do not know its exact expiration date. Is there any way we can calculate it from the given data?

In the SQL server, we have the DATEADD() function, which helps us add date intervals to a given date. For example, we can run the following query on the SQL server:

SELECT DATEADD(day, 1, '2022-08-26') as Next_Day;

Output:

The output returned results from adding 1 day to the date specified. Similarly, the DATEADD() function allows us to add other types of date intervals, such as months and years.

Having a DATEADD() function is useful because the addition and subtraction of dates are not as straightforward as with numbers. We must remember that the month and year’s value must be incremented after specific days, and days must also be restarted from 1 after some time.

However, PostgreSQL does not provide us with a similar DATEADD() function as the SQL server. So how do we perform essential date calculations in PostgreSQL?

This article will discuss alternatives to the DATEADD() function in PostgreSQL.

Use the + and - Operators as Alternative to DATEADD() in PostgreSQL

One way to do this is to use the + and - operators like we would for integer addition and subtraction. Let us see how we can add a certain number of days to a date using the + operator.

SELECT date '2022-08-24' + 10 as Expiration_Date;

This will generate the following output:

We can see that 10 days have been added to the specified date as expected. Another way to write this query is as follows, and it generates the same result:

SELECT date '2022-08-24' + integer ‘10’ as Expiration_Date;

Output:

Next, let us see how we can go back in time and subtract a given number of days from a date using the + and - operators. The first way to do so is by using a + operator and giving a negative integer value, as shown below:

SELECT date '2022-09-03' + -10 as Manufacture_Date;

OR

SELECT date '2022-09-03' + integer ‘-10’ as Manufacture_Date;

The output is shown below:

The second way is to use the - operator and specify the number of days we want to subtract. This is done in the following way:

SELECT date '2022-09-03' - 10 as Manufacture_Date;

OR

SELECT date '2022-09-03' - integer ‘10’ as Manufacture_Date;

The output is shown below:

This method of using the + and - operators is useful when you only have to add a specific number of days.

What if you want to add 2 months to date? You can always convert the 2 months into several days and then use the + operator to add, but this is a long process.

Alternatively, we can use the INTERVAL data type to add any date intervals straightforwardly. This method is explained below.

Use the INTERVAL Data Type as Alternative to DATEADD() in PostgreSQL

The INTERVAL data type stores days, months, years, weeks, and time in hours, minutes, and seconds. By running some sample queries, let us demonstrate how each is used.

Days Interval

A specific number of days can be added to a date in the following way:

SELECT date '2022-08-24' + INTERVAL ‘10 day’ as Expiration_Date;

This gives the following output:

We can see that the output is displayed as a timestamp without a time zone. This is because the INTERVAL data type also deals with time values, so the date specified in our query has been converted to having both date and time values as a timestamp.

Months Interval

A specific number of months can be added to a date in the following way:

SELECT date '2022-08-24' + INTERVAL ‘2 month’ as Expiration_Date;

This gives the following output:

Years Interval

A specific number of years can be added to a date in the following way:

SELECT date '2022-08-24' + INTERVAL ‘1 year’ as Expiration_Date;

This gives the following output:

Weeks Interval

A specific number of weeks can be added to a date in the following way:

SELECT date '2022-08-24' + INTERVAL ‘1 week’ as Expiration_Date;

This gives the following output:

Multiple Date Intervals

We can also use the INTERVAL data type to add multiple types of date intervals at a time. An example is shown below:

SELECT date '2022-08-24' + INTERVAL ‘1 week 2 day’ as Expiration_Date;

This gives the following output:

Note: Apart from these, we can also add time intervals to a date using the INTERVAL data type by specifying the exact hours, minutes, or seconds to add.

Date Intervals in Variables

An example at the start of the article discussed calculating the expiration date from a stored value of the manufacture date and the number of days till expiry. Let us see how we can perform calculations on dates using values stored in variables.

The syntax is as follows:

SELECT manufacture_date + expires_in * INTERVAL '1 day' as Expiration_Date FROM product;

In this query, we have converted the value stored in the expires_in variable to an INTERVAL representing days. This gives the following output:

Similarly, we can also convert values stored as integers to months, years, and weeks and perform date calculations on them.

This sums up our discussion regarding alternatives to the DATEADD() function in PostgreSQL. Keep learning!