How to Add Days to Date in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Create a Table Having a Field With Date Type
  2. Add Days With the date
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.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Date