CODEWITHSUNDEEP
postgresqldatetime
Salil
Salil

Reputation: 47542

How to add number of days in postgresql datetime

I have the 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 the calculation deadline = created_at + claim_window, where claim_window is the number 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 tried something like following, but it didn't work.

SELECT * FROM "projects" 
WHERE (DATE_PART('day', now()- created_at) >= (claim_window+1))

Upvotes: 151

Views: 283571

Answers (4)

Maxim Dorofeev
Maxim Dorofeev

Reputation: 21

I would suggest this if you need to add the number of days to the timestamp: SELECT (now() + (:count_days * INTERVAL '1 days'))::timestamp;

Upvotes: 0

Sam Coorg
Sam Coorg

Reputation: 473

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

Upvotes: 11

user330315
user330315

Reputation:

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

Upvotes: 227

Jason Silver
Jason Silver

Reputation: 756

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

Postgres Date/Time Functions

Upvotes: 35

Related Questions