How to add number of days in postgresql datetime
PostgresqlDatetimePostgresql Problem Overview
I have a following table projects.
id title created_at claim_window
1 Project One 2012-05-08 13:50:09.924 5
2 Project Two 2012-06-01 13:50:09.924 10
A) I want to find the deadline with calculation deadline = created_at + claim_window(No. of days).
Something like following.
id title created_at claim_window deadline
1 Project One 2012-05-08 13:50:09.924 5 2012-05-13 13:50:09.924
2 Project Two 2012-06-01 13:50:09.924 10 2012-06-11 13:50:09.924
B) I also want to find the projects whose deadline is gone
id title created_at claim_window deadline
1 Project One 2012-05-08 13:50:09.924 5 2012-05-13 13:50:09.924
I try something like following.
SELECT * FROM "projects"
WHERE (DATE_PART('day', now()- created_at) >= (claim_window+1))
But for some reason it is not working.
Postgresql Solutions
Solution 1 - Postgresql
This will give you the deadline :
select id,
title,
created_at + interval '1' day * claim_window as deadline
from projects
Alternatively the function make_interval can be used:
select id,
title,
created_at + make_interval(days => claim_window) as deadline
from projects
To get all projects where the deadline is over, use:
select *
from (
select id,
created_at + interval '1' day * claim_window as deadline
from projects
) t
where localtimestamp at time zone 'UTC' > deadline
Solution 2 - Postgresql
For me I had to put the whole interval in single quotes not just the value of the interval.
select id,
title,
created_at + interval '1 day' * claim_window as deadline from projects
Instead of
select id,
title,
created_at + interval '1' day * claim_window as deadline from projects
Solution 3 - Postgresql
you can just use the below code to append or substract any date field
select date('08/30/2021') + 180 ---it will give next 180 days date
select current_date + 180 ---it will give next 180 days date
select current_date - 180 ---it will give before 180 days date