Monday, June 7, 2010

Add days to timestamp

Out of the box, this doesn't work:

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)

2 comments:

  1. Why is that better than "select now() + interval '7' day"

    ReplyDelete
  2. This 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


    "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

    ReplyDelete