How to Add Days to Date in PostgreSQL
Postgres has the date
type data that we can use to store date
information with different styles. Postgres can also increase or decrease date
or time with specific units.
This tutorial will teach us how to add many days in date
while using PostgreSQL.
Create a Table Having a Field With Date
Type
We have a project
table containing different fields/columns. Notice that this table has its creation date
, and there’s another column with an integer value that we want to add with the date
and show it.
create table project (
id INT,
Project VARCHAR(50),
Created DATE,
Interval INT
);
insert into project (id, Project, Created, Interval)
values
( 1, 'Heron Therapeutics, Inc.', '2021-10-23', 9),
( 2, 'CryoPort, Inc.', '2022-05-07', 5),
( 4, 'Turtle Beach Corporation', '2022-07-27', 10),
( 5, 'Banco Santander Chile', '2022-02-08', 6),
( 6, 'BLACKROCK INTERNATIONAL, LTD.', '2022-07-28', 10),
( 7, 'Sohu.com Inc.', '2021-10-14', 12),
( 8, 'Northeast Bancorp', '2022-07-20', 6);
select * from project;
Output:
CREATE TABLE
INSERT 0 7
id | project | created | interval
----+-------------------------------+------------+----------
1 | Heron Therapeutics, Inc. | 2021-10-23 | 9
2 | CryoPort, Inc. | 2022-05-07 | 5
4 | Turtle Beach Corporation | 2022-07-27 | 10
5 | Banco Santander Chile | 2022-02-08 | 6
6 | BLACKROCK INTERNATIONAL, LTD. | 2022-07-28 | 10
7 | Sohu.com Inc. | 2021-10-14 | 12
8 | Northeast Bancorp | 2022-07-20 | 6
(7 rows)
Add Days With the date
We can use the interval
method inside our query to get the claim date
. So, we will add the interval
with the created
column and print it. The query will be like the following:
select id,
project,
created,
interval,
date(created + interval '1 day' * interval) as deadline
from project;
Here’s the output:
id | project | created | interval | deadline
----+-------------------------------+------------+----------+------------
1 | Heron Therapeutics, Inc. | 2021-10-23 | 9 | 2021-11-01
2 | CryoPort, Inc. | 2022-05-07 | 5 | 2022-05-12
4 | Turtle Beach Corporation | 2022-07-27 | 10 | 2022-08-06
5 | Banco Santander Chile | 2022-02-08 | 6 | 2022-02-14
6 | BLACKROCK INTERNATIONAL, LTD. | 2022-07-28 | 10 | 2022-08-07
7 | Sohu.com Inc. | 2021-10-14 | 12 | 2021-10-26
8 | Northeast Bancorp | 2022-07-20 | 6 | 2022-07-26
It will also work without the interval
method. We can also write the query as:
select id,
project,
created,
interval,
date(created + interval) as deadline
from project;
Output:
id | project | created | interval | deadline
----+-------------------------------+------------+----------+------------
1 | Heron Therapeutics, Inc. | 2021-10-23 | 9 | 2021-11-01
2 | CryoPort, Inc. | 2022-05-07 | 5 | 2022-05-12
4 | Turtle Beach Corporation | 2022-07-27 | 10 | 2022-08-06
5 | Banco Santander Chile | 2022-02-08 | 6 | 2022-02-14
6 | BLACKROCK INTERNATIONAL, LTD. | 2022-07-28 | 10 | 2022-08-07
7 | Sohu.com Inc. | 2021-10-14 | 12 | 2021-10-26
8 | Northeast Bancorp | 2022-07-20 | 6 | 2022-07-26
(7 rows)
Here, we can see that the deadline
column adds the interval amount of the days with the created
column. To know more about the date and time in Postgres, visit the following official documentation.