How to add number of days in postgresql datetime

Question

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))

score 234 · Accepted Answer · 2020-12-07 13:35:07Z

234

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

edited Dec 7, 2020 at 13:35

answered Jun 6, 2012 at 7:36

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Salil Over a year ago

but one more issue is occur my created_at has datatype timestamp without time zone AND current_timestamp has datatype timestamp with time zone, hence i don't get correct answer

user330315 Over a year ago

@Salil: LOCALTIMESTAMP is a timestamp without time zone

Erwin Brandstetter Over a year ago

@Salil: It also does not matter, the result would be the same with either timestamp or timestamptz, as long as the data is referring to the same point in time. Adding a day has the same effect on both.

Salil Over a year ago

my create_at is get saved in the UTC time (W/o timezone) and LOCALTIMESTAMP & CURRENT_TIMESTAMP gives me time in the +5.30, hence probles is still not get solved

user330315 Over a year ago

@Salil: then re-calculate the localtimestamp to utc using localtimestamp at time zone 'UTC'

|

Jason Silver · Accepted Answer · 2018-11-08 08:10:53Z

36

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

edited Nov 8, 2018 at 8:10

user330315

answered Jun 13, 2018 at 6:13

Jason Silver

7667 silver badges10 bronze badges

1 Comment

VishnuVS Over a year ago

created_at + '1 day' worked for me as well.

zb226 · Accepted Answer · 2022-10-05 15:10:30Z

12

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

edited Oct 5, 2022 at 15:10

zb226

10.7k6 gold badges57 silver badges91 bronze badges

answered Aug 30, 2021 at 11:02

Sam Coorg

4834 silver badges6 bronze badges

Comments

Maxim Dorofeev · Accepted Answer · 2024-02-22 10:26:02Z

1

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

answered Feb 22, 2024 at 10:26

Maxim Dorofeev

313 bronze badges

Collectives™ on Stack Overflow

How to add number of days in postgresql datetime

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related