select CURRENT_TIMESTAMP + 7
Solution:
create function add_days_to_timestamp(t timestamptz, d int)
returns timestamptz
as
$$
begin
return t + interval '1' day * d;
end;
$$ language 'plpgsql';
To use:
select add_days_to_timestamp(current_timestamp, 3)
Oh.. I forgot, I said the following doesn't work out-of-the-box...
select CURRENT_TIMESTAMP + 7
..., so we must make it that it look and feels like an out-of-the-box functionality. Just add the following operator to make it look like and behave like one:
create operator + (leftarg = timestamptz, rightarg = int,
procedure = add_days_to_timestamp);
To test:
select CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 7 as one_week_after
Output:
now | one_week_after
-------------------------------+-------------------------------
2010-06-07 02:33:34.551716+08 | 2010-06-14 02:33:34.551716+08
(1 row)
Why is that better than "select now() + interval '7' day"
ReplyDeleteThis blog post's context is from stackoverflow, it's a response to an stackoverflow question http://stackoverflow.com/questions/838478/adding-sum-of-current-timestamp-and-days-column-in-postgres
ReplyDelete"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 is a column in the same table."
I dwell too much on the poster's question, i.e. adding an integer directly to a date would automatically add days to it. I forgot it's more elegant to do like what you've said, i.e. I should have given him this answer:
create table foo
(
time timestamp,
days int
);
insert into foo values(null, 7);
update foo set time = now() + days * interval '1 day';
Thanks for noticing that, will advise other Postgres users to use the built-in interval instead