When working with PostgreSQL databases, you often encounter such scenarios where you need to add a specific number of days to a given date.
This tutorial explores the various methods and techniques of adding days to a given data in PostgreSQL. This can be helpful when you need to calculate the future or past dates quickly and efficiently using the date-based queries.
Setup a Test Table
If you do not yet have a date table, you can quickly create one as the following query provides:
id SERIAL PRIMARY KEY,
date_col DATE
);
The previous statement creates a table called test_dates with id and date_col columns.
It is good to ensure that the target column contains the date types as PostgreSQL may raise an error using some of the methods that are discussed in this post.
Once we have the test table created, we can add some random data into the table as shown in the following:
VALUES
('2023-05-01'),
('2023-05-02'),
('2023-05-03'),
('2023-05-04'),
('2023-05-05'),
('2023-05-06'),
('2023-05-07'),
('2023-05-08'),
('2023-05-09'),
('2023-05-10');
This should add the defined rows to the table and return the resulting table as shown in the following output:
Method 1: PostgreSQL Add Days to Date
PostgreSQL’s first and most common method of adding days to a date is using the addition operator with the INTERVAL keyword. The INTERVAL keyword allows us to specify the duration to add to the provided date value.
We can express this syntax as shown in the following:
Replace the value of n with the number of days that you want to add.
Let us take the test_date table as an example. Suppose we wish to add five days to each of the rows in the col_date column.
We can run the query as shown in the following:
The previous clause adds five days to each of the provided date. This should return the table as shown in the following:
Method 2: PostgreSQL Add Days to Date
The second method is similar to the one that is previously described. However, instead of passing the days to add as a literal value, we define a variable and use the :: operator.
Consider the following example:
DECLARE
days_to_add INTEGER := 7;
result_dates DATE[];
BEGIN
-- Add days to the date using the variable
result_dates := ARRAY(
SELECT date_col + (days_to_add::INTEGER || ' days')::INTERVAL
FROM test_dates
);
-- Display the result dates
RAISE NOTICE 'Result Dates: %', result_dates;
END $$;
This should return the dates from the input table with 7 days added.
Conclusion
We explored the two primary methods that you can use to add a specific number of days to a date column in PostgreSQL using the INTERVAL keyword. Feel free to modify the examples to fit your requirements.
