如何在postgresql datetime中添加天数

问题描述

我有一个下表项目。

  id title created_at claim_window 
 1项目一2012-05-08 13：50：09.924437 5 
 2项目二2012-06-01 13：50：09.924437 10 
  pre> 
 
 
 A）我想通过计算找到截止日期 deadline = created_at + claim_window（天数） 
 
 
 
如下所示。
  id title created_at claim_window deadline 
 1项目一2012-05-08 13：50：09.924437 5 2012-05-13 13：50：09.924437 
 2项目二2012-06-01 13：50：09.924437 10 2012-06-11 13：50：09.924437 
  
 B]我也想找到截止日期的项目
  id title created_at claim_window截止日期
 1项目一2012-05-08 13：50：09.924437 5 2012-05-13 13 ：50：09.924437 
  
我尝试如下。
  SELECT * FROMprojectsWHERE（DATE_PART（'day'，now（） -  created_at）> =（claim_window + 1））
  pre> 
 
 
但由于某种原因，它不起作用。
解决方案
这将给你截止日期：
  select id，
 title，
 created_at + interval'1' day * claim_window作为截止日期
从项目
  
要获得截止期限结束的所有项目，使用：
  select（
 select id，
 created_at + interval'1 'day * claim_window作为截止日期
从项目
）t 
其中localtimestamp在时区'UTC'>截止日期
  
 
I have a following table 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).
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 
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.
 解决方案 
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
                        
这篇关于如何在postgresql datetime中添加天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！