PostgreSQL Date Add + Add days to date in PostgreSQL Examples

In this Postgresql tutorial, we will learn about “Postgresql date add” which means we will add the month, year, hours to the current date.

We will also see, how to add days to date in PostgreSQL with a few more examples.

And we are going to cover the following topics.

• postgresql date add month
• postgresql date add year
• postgresql date add days
• postgresql date add hours
• postgresql date add minutes
• postgresql date add interval
• Postgresql add days to date column
• Postgresql add days to date current date
• Postgresql add business days to date
• Postgresql add 7 days to date
• Postgresql add x days to date

Postgresql date add

Before beginning, we need to know that Postgresql does not have 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 or one day to the current date, which means if today is 2021-09-28, then it will add an interval of one day after this operation it will become 2021-09-29.

• INTERVAL: It is a data type that allows us to store and manipulate periods of time like years, months, hours, etc.

Below is the INTERVAL INPUT FORMAT.

Syntax:

quantity unit (quantity unit...) direction

• quantity: It is a number, symbol like +, –.
• unit: It is a unit of time like a month, year, second, hour, century, week, decade, etc.
• direction: It is ago or empty string ‘ ‘.

The output of the above code is given below.

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

• current_date: It is used to get the current date only.
• now: It is used to get the current date with timezone.

Read: Postgresql date between two dates

Postgresql date add month

In Postgresql, Let’s add a month to the current date with the help of interval data type.

First, find the current date using the current_date function.

SELECT current_date;

It will show current dates like 2021-09-29 where 2021 is the year, 09 is the month and 29 is the day, so we have 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

Postgresql date add 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 typecast (::) operator to convert a value of one datatype into another. So we have converted the ‘2022-09-18’ string to 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 above code is given below.

Read: PostgreSQL DATE_PART() Function

Postgresql date add 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 above code is given below.

Using INTERVAL

SELECT current_date + INTERVAL '5 days';

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

The output of the above code is given below.

Read: PostgreSQL DATE Functions

Postgresql date add hours

In Postgresql, we can add hours to the current date using INTERVAL, but here we will use the now( ) function that returns date with timezone.

Let’s find the current date with 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 hour, it will become 22 hours.

Now we will add 10 more hours to the current date hour using the below code.

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

Postgresql date add 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 below code.

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 from 20 to 40 minutes.

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

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

Read: How to create a view in PostgreSQL

Postgresql date add interval

In all the above sub-section of the tutorial, we are using intervals so if you want to learn about “How to date add interval” then go through all the above sub-section.

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

Postgresql add days to date column

In Postgresql, we can add the days to date column that already exists in databases, let’ 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');

Think about that expiry_date column contains the expiry date of software products, and users want to extend the expiry date for certain days, let’ extend the expiry date that exists between date 2020-04-01 and 2020-04-07 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 the 30 days to the date column.

Postgresql add days to date current date

In Postgresql, the number of days can be added to the current date or present date with the help of the INTERVAL data type.

Here we will use the current_date( ) function to get the current date and 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, the current date is 2021-10-04, and changed to 2021-10-14 after adding 10 days.

Postgresql add business days to date

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

Let’s run the below query to add business days to 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 which contains two columns date_d and day_of_week.

Using generate_series function, we are generating a series of dates from 2021 Apr 28 to 2021 Jun 02 and storing in column date_d.

Within SELECT statement of WITH clause, we are also extracting the day of week using extract(DOW FROM date_d) function from column date_d, and storing this extracted day of week in another column name 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.

Postgresql add 7 days to date

In Postgresql, we can add 7 days to date or any date, let’s see with the example.

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

From the above code, we are adding 7 days to date 2002-6-07 and after adding 7 days, it will become 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 above code is given below.

Postgresql add x days to date

In Postgresql, we can add x days to date or add any number of days to any date using the function, so we will create a function using the procedure that will add days to any date.

Let’ 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 are creating a function add_days that accepts two-parameter days (of interval type) and date (of date type), then in a SELECT statement adding days to new_date and storing in the variable named added_date and returning the added_date from 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 date 2021-07-04 and changed to 2021-07-7 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.

You may also like to read the following articles.

• 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

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

• postgresql date add month
• postgresql date add year
• postgresql date add days
• postgresql date add hours
• postgresql date add minutes
• postgresql date add interval
• Postgresql add days to date column
• Postgresql add days to date current date
• Postgresql add business days to date
• Postgresql add 7 days to date
• Postgresql add x days to date