PostgreSQL Date Add

In this PostgreSQL tutorial, we will learn about “PostgreSQL date add,” which means we will add the month, year, and hours to the current date with multiple examples.

PostgreSQL date add

Before beginning, we need to know that PostgreSQL does not have the DATEADD( ) function like other databases such as SQL, MYSQL, etc. But we can achieve the same result using DateTime arithmetic with interval literals.

For example.

SELECT current_date + INTERVAL '1 day';

In the above code, we are adding an extra day to the current date. This means that if today is 2021-09-28, the operation will add one day, resulting in 2021-09-29.

• INTERVAL: It is a data type that allows us to store and manipulate periods, such as years, months, and hours.

Below is the INTERVAL INPUT FORMAT.

Syntax:

quantity unit (quantity unit...) direction

• Quantity: It is a number, represented by a symbol such as + or –.
• Unit: It is a unit of time, such as a month, year, second, hour, century, week, or decade.
• Direction: It is an empty string.

The output of the code above is provided below.

We will use two functions, current_date and now, in this tutorial.

• current_date: It is used to get the current date only.
• Now: It is used to retrieve the current date with the time zone.

Read: PostgreSQL date between two dates

Example 1: Adding a month

In PostgreSQL, let’s add a month to the current date using the interval data type.

First, find the current date using the current_date function.

SELECT current_date;

It will display current dates like 2021-09-29, where 2021 is the year, 09 is the month, and 29 is the day. Therefore, we need to add a month to the current month. For that, we will use INTERVAL ‘1 month’ on the current date, which increases the month from 09 to 10.

An example is given below.

SELECT current_date + INTERVAL '1 month';

Read: PostgreSQL date_trunc function

Example 2: Adding a year.

In PostgreSQL, we can also add a year to the current date using the INTERVAL data type.

Let’s add a year to any date.

SELECT '2022-09-18':: date + INTERVAL '1 year';

In the above code, we have used the typecast (::) operator to convert a value of one datatype into another. So we have converted the ‘2022-09-18’ string to a date datatype and added a year using INTERVAL ‘1 year’.

Syntax

expression :: datatype like '2'::integer

After adding a year to 2022-09-18 became 2023-09-18.

The output of the code above is provided below.

Read: PostgreSQL DATE_PART() Function

Example 3 Adding days

In PostgreSQL, we can add days in two ways, one using only simple arithmetic operators like a plus (+) and the other using INTERVAL.

Using a simple arithmetic operator

SELECT current_date + 5;

The above code will add 5 days to the current day. If today is 2021-09-29, then it will become 2021-09-04 after adding 5 days to it.

The output of the code above is provided below.

Using INTERVAL

SELECT current_date + INTERVAL '5 days';

The above code will add 5 days to the current date using the INTERVAL function.

The output of the code above is provided below.

Read: PostgreSQL DATE Functions

Example 4: Adding hours

In PostgreSQL, we can add hours to the current date using the INTERVAL function, but here we will use the now() function, which returns a date with a time zone.

Let’s find the current date with the timezone using the now() function.

SELECT now();

The above code will show the current date with timezone, If the current date with timezone is ‘2021-09-29 12:49:08.843286+05:30’ where after date part is hours which is 12 so the current hour is 12. After adding 10 hours to the current date, it will become 22 hours.

Now, we will add 10 more hours to the current date and time using the code below.

SELECT now() + INTERVAL '10 hours';

In the above code, we have added the 10 hours to the current date hour and the current hour changed from 12 to 22 hours given below in the output.

Read: PostgreSQL DATE Format

Example 5: Adding minutes

In PostgreSQL, we can also add minutes to the current date using INTERVAL. Let’s understand with an example.

Fetch the current date with timezone using the code below.

SELECT now();

The above code will return the current date with timezone if the current date with timezone is ‘2021-09-29 13:20:04.444994+05:30’ where after the hour part is minute that is 20. So, we are going to add 20 more minutes to it, and it will become 40 minutes instead of 20.

Let’s add 20 minutes to the current date using the code below.

From the above output, we have added 20 minutes to the current date.

Read: How to create a view in PostgreSQL

Example 6: PostgreSQL add days to date

First, in Postgresql, we need to know about the current_date( ) that is used to get the current date without a timezone, INTERVAL is a data type that we can use to manipulate the date in days, weeks, months, etc, and typecast operator(::)that can be used for casting from string to date or interval data type.

Read PostgreSQL group by

Example 7: Adding days to the date column

In PostgreSQL, we can add days to the date column that already exists in databases. Let’s understand through an example.

Create the table named date_days.

CREATE TABLE date_days(product_id SERIAL,expiry_date DATE);

In the above code, we have created two columns named product_id, expiry_date in a table named date_days.

• product_id: It is the id of a specific product.
• expiry_date: It is the expiry date of the product.

Insert the following records in the table.

INSERT INTO date_days(expiry_date)VALUES('2020-04-01'),
('2020-04-04'),
('2020-04-05'),
('2020-04-07'),
('2020-04-08'),
('2020-04-10'),
('2020-04-11'),
('2020-04-12');

Consider the expiry_date column, which contains the expiry date of software products, and users want to extend the expiry date for a certain number of days. Let’s extend the expiry date that exists between 2020-04-01 and 2020-04-07 for the product for 30 days.

UPDATE date_days
SET expiry_date = expiry_date + INTERVAL '30 day'
WHERE expiry_date BETWEEN '2020-04-01' AND '2020-04-07';

SELECT * FROM date_days;

In the above code, we have used the UPDATE command to update the column data in PostgreSQL.

From the above output, we can see that we have added 30 days to the date column.

Example 8: Add days to the current date

In PostgreSQL, the number of days can be added to the current date or the present date using the INTERVAL data type.

Here we will use the current_date( ) function to get the current date and the INTERVAL data type to add the days to the current date.

SELECT current_date + INTERVAL '10 day';

In the above code, we are adding 10 days to the current date.

From the above output, we have added 10 days to the current date, which is 2021-10-04, and changed it to 2021-10-14 after adding 10 days.

Example 9: Adding Business Days to a Date.

In PostgreSQL, we can also add the business days to a date. Business days are working days of the week from Monday to Friday, which represent the 5 days of the week.

Let’s run the below query to add business days to the date.

with business_days AS
(
    SELECT date_d, extract(DOW FROM date_d) day_of_week
    FROM generate_series('2021-04-28'::date, '2021-06-02'::date, '1 day'::interval) date_d
)
SELECT date_d + INTERVAL '2 day',day_of_week
FROM  business_days
WHERE  day_of_week NOT IN (6,0);

From the above code, we are creating a temporary table named ‘business_days’ that contains two columns: ‘date_d’ and ‘day_of_week’.

Using the generate_series function, we generate a series of dates from April 28, 2021, to June 2, 2021, and store them in the date_d column.

Within the SELECT statement of the WITH clause, we also extract the day of the week using the extract(DOW FROM date_d) function from the date_d column and store this extracted day of the week in another column named day_of_week.

After WITH clause in SELECT date_d + INTERVAL ‘2 day’,day_of_week FROM business_days WHERE day_of_week NOT IN (6,0) code, we are fetching date from date_d column by add 2 days to each date which is a business day and filtering business day using WHERE clause that will show date which is not a day_of_week as 6 or 0.

Example 10: Adding 7 days to a date

In PostgreSQL, we can add 7 days to a date or any date, let’s see with an example.

SELECT '2002-06-07'::date + INTERVAL '7 day';

From the above code, we are adding 7 days to the date 2002-06-07, and after adding 7 days, it becomes the date ‘2002-06-14’.

Date ‘2002-06-7’::date means we are casting a string to date data type where ( expression::datatype ) is typecast, operator.

The output of the code above is provided below.

Example 11: Adding x days to a date

In PostgreSQL, we can add a specified number of days to a date using the function. Therefore, we will create a function using a procedure that adds days to any date.

Let’s create a function named add_days.

CREATE OR REPLACE FUNCTION add_days(days interval,new_date date) RETURNS date
AS $$
DECLARE
added_date date;
        BEGIN
                SELECT new_date + days INTO added_date;
				RETURN added_date;
        END;
$$ LANGUAGE plpgsql;

In the above code, we create a function named add_days that accepts two parameters: days (of interval type) and date (of date type). Then, in a SELECT statement, we add days to new_date and store it in the variable named added_date, returning added_date from the function.

Let’s call the function with dates 2021-4-07 and 3 days.

SELECT * FROM add_days(INTERVAL '3 day','2021-07-04'::date

From the above output, we have added 3 days to the date 2021-07-04 and changed it to 2021-07-07 after adding 3 days.

In the above code, In calling statement of the function named add_days, we can input different numbers to the first parameter and date to the second parameter like SELECT * FROM add_days(INTERVAL ‘5 day’,’2020-04-10′::date), this will add 4 days to date 2020-04-10, and after adding it will become 2020-04-15.

So, in this tutorial, we have learned about “Postgresql date add” and Postgresql add days to date with various examples.

You may also find the following articles of interest.

• PostgreSQL create user with password
• PostgreSQL create database
• PostgreSQL list databases
• PostgreSQL Data Types
• PostgreSQL ilike case insensitive
• PostgreSQL group_concat
• How to set user password in PostgreSQL