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 forgot to mention that "days" is a column in the same table, not constant.egaga– egaga2009-05-08 06:54:25 +00:00Commented May 8, 2009 at 6:54
Add a comment
|
3 Answers
select now() + cast('1 day' as interval) * 3 -- example: 3 days
1 Comment
QuirkyBit
This was the actually exactly what I needed. I am not sure why this isn't part of your answer @Michael Buen
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);
Now this would work:
update foo set time = current_timestamp + 3 /* day variable here,
or a column from your table */
Note:
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
1 Comment
egaga
Thank you, this was really eye opener for what I can do with postgresql
calculatedDate timestamp without time zone;
calculatedDate := current_timestamp + interval '1' day * days_count;
Comments
