如何在 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-10 23:32:36  来源:igfitidea点击:

How to add number of days in postgresql datetime

postgresqldatetime

提问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   

Postgres Date/Time Functions

Postgres 日期/时间函数