如何在 postgresql 日期时间中添加天数
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/10909902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add number of days in postgresql datetime
提问by Salil
I have a following table projects
.
我有一个下表projects
。
id title created_at claim_window
1 Project One 2012-05-08 13:50:09.924437 5
2 Project Two 2012-06-01 13:50:09.924437 10
A) I want to find the deadline with calculation deadline = created_at + claim_window(No. of days)
.
A)我想通过计算找到截止日期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.924437 5 2012-05-13 13:50:09.924437
2 Project Two 2012-06-01 13:50:09.924437 10 2012-06-11 13:50:09.924437
B] I also want to find the projects whose deadline is gone
B] 我也想找到截止日期已过的项目
id title created_at claim_window deadline
1 Project One 2012-05-08 13:50:09.924437 5 2012-05-13 13:50:09.924437
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.
但由于某种原因它不起作用。
回答by a_horse_with_no_name
This will give you the deadline :
这会给你最后期限:
select id,
title,
created_at + interval '1' day * 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
回答by Jason Silver
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