在Postgres中添加current_timestamp和days列的总和 [英] Adding sum of current_timestamp and days column in Postgres

问题描述

我想通过将天数添加到当前时间来更新一列。用伪语法是：

I want update a column by adding days to current time. In pseudosyntax it would be:

UPDATE foo
SET time = current_timestamp + days::integer

days是同一表中的一列。

days is a column in the same table.

推荐答案

create function add_days_to_timestamp(t timestamptz, d int) 
returns timestamptz
as
$$
begin
    return t + interval '1' day * d;
end; 
$$ language 'plpgsql';


create operator + (leftarg = timestamptz, rightarg = int, 
         procedure = add_days_to_timestamp);

现在这可以工作：

update foo set time = current_timestamp + 3 /* day variable here, 
or a column from your table */

注意：

由于某些原因，内置了一个整数在Postgres中，这将起作用：

for some reason, adding an integer to date is built-in in Postgres, this would work:

select current_timestamp::date + 3 -- but only a date

这不会（除非您定义自己的运算符，请参见上文）：

this would not(unless you define your own operator, see above):

select current_timestamp + 3

这篇关于在Postgres中添加current_timestamp和days列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！