如何添加日期后的天数

我有一个下面的表 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)我想用计算 deadline = created_at + claim_window(No. of days)找到最后期限。

比如跟踪。

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

我试着跟着做。

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

But for some reason it is not working.

216007 次浏览
小开
最佳答案

这会给你最后期限:

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

要获得所有截止日期已过的项目，使用:

select *
from (
select id,
created_at + interval '1' day * claim_window as deadline
from projects
) t
where localtimestamp at time zone 'UTC' > deadline
小开

对我来说，我必须把整个间隔放在单引号中，而不仅仅是间隔的值。

select id,
title,
created_at + interval '1 day' * claim_window as deadline from projects

而不是

select id,
title,
created_at + interval '1' day * claim_window as deadline from projects

日期/时间函数

小开

您可以使用下面的代码来附加或减去任何日期字段

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

提交答案