Postgres add days to date

Postgresql uses a DATE data type to store values in the form of dates. This article will throw light on adding days to the dates through different commands in PostgreSQL.

We simply use the select command with this keyword to display the current date. The current_date is fetched from the system where the PostgreSQL database is configured. So, we will use this command to see the date for now. Postgresql follows a standard format of the date. That is ‘yyyy-mm-dd’.

Now we will add a single day to the current date. Interval is a data type that manipulates the data in the form of days, months, weeks. etc. By doing this, the timestamp will display the date without the time zone. Only the format for the time is displayed along with the current date.

As we have mentioned above, the current date is 19 February, so in addition to one day on the current day, it will become 20-02-2022.

Similarly, if we simply add days in the current date without using the word ‘interval’ will display only the date.

So from the resultant value, you can see that only the date after 4 days is displayed. Now, if we add the keyword interval in the same above command, the resultant value will be displayed again along with the time zone.

Instead of using the keyword ‘current_date’, we will directly use the date to which we want to add days. With the keyword interval, 7 days will be added.

This will add 7 days to the 27th of June. By doing this, the month will be changed and shifted towards July.

Till now, we have seen simple dates format and the addition of days in the date. But now, we will see the addition of days in the dates present in the table. A table named ‘date_days’ is created with the columns having ids of items and the expiry dates of the product. The date is a built-in feature of PostgreSQL, so we will follow the date format while entering data.

After creating the table, we will now add some data by inserting rows in the table.

After inserting data, you can see that a message is displayed that 8 rows are inserted in the table. We have not entered ids in the column of item_id, because the numeric values are automatically generated by the postgresql.

Now we will see the record from the select statement.

The expiry_date column will be modified by adding days to the dates in each row. We have applied a condition here in which 10 days will be added to those expiry dates of products that lie in a specific range we have used in the command. For modification, we have used an UPDATE command; the column name that is to be affected is mentioned after the keyword ‘set’ in the update command. Furthermore, a select statement is used to display all the records of the table to see the alteration we have applied.

From the resultant value, you can observe that, on execution, the dates between the first of April 2020 to the 7th of April 2020 will be affected, and 10 days will be added to their current dates in each row. While other data having the expiry_dates below 1st April and above 7th April will have remained unaffected. The ids from 5 to 8 will be displayed as it is. While id from 1 to 4 will be displayed with the addition of 10 days. All those rows that are affected by the command will be displayed collectively at the end of the relation.

Add business days to the date

We will add the business days to the date mentioned in the command. Postgresql, provide this facility to add specified rows directly. Business days are the working days of the week starting from Monday to Friday. These are 5 days a week.

We have seen the addition of days by using the number of days in the command, but now we will add days in the date by using business days.

A temporary table is created from the above code that has two columns date_d, and day_of_week. By using a built-in function of Postgres, Generating_series(), we will generate dates between a range of two dates. And these dates will be stored in column date_d.

Inside the select statement of with clause, the day of a week is extracted by using an extract function (DOW FROM date_d) function from the date_d column, and then these extracted dates will be stored in the other column.

The second select statement contains the addition with the interval of 2 days, day of the week from business_days. We have applied a filter here to fetch the date from the date column by adding 2 in each date that is a business day. And by using a WHERE clause, that will display all the days except those that are of 6 or 0.

Now we will see the first column having dates with the addition of two on each date. As we have started from 10-02-2022, so add 2 in it, it will become 12; this is what the first row contains that date. Then again, an interval of 4 dates is applied, so it has become 16 until the date 27 is reached. Now talking about the second column that is day_of_week that, will display the day number from 1 to 5 to display the business days only. As the original date is 10, so on 10th February 2020, it is Thursday, and according to numbering, it is the 4th day of the week. Similar logic is applied to the remaining rows.

Add day by using a Function

A function is created to add days to the date. This function will take parameters of days’ interval, new_date. And it returns the date on calling the function. The logic contains the select statement that shows the new_date and the days that are to be added.

Now let us call the function.

This will return the date of having 3 days added to the date provided.

Conclusion

The article contains the date function, in which days are added to the specified dates, either the current dates or those that are written by the user manually. We have applied simple commands and also on the tables as well. This feature of PostgreSQL helps the user in manipulating data by altering the dates to fetch the record of a particular date.