i want in postgres add in a column daterange(called period) some days.
for example, if daterange is
26-09-2014 : 28-09-2014
i want add 2 days and the result must be another daterange
28-9-2014 : 30-09-2014
How can i do? i try
select PERIOD, PERIOD + '2 day' from mytable
You need to add the value to each element and then create a new daterange out of that:
select daterange(lower(period) + 2, upper(period) + 2)
from mytable;
Note that you can not add an interval (e.g. interval '2' day) to the date value because that returns a timestamp and thus that value cannot be converted to a daterange
select daterange(lower(r) + 2, upper(r) + 2)
from (values
(daterange(current_date, current_date + 2))
) s(r);
daterange
-------------------------
[2014-09-28,2014-09-30)
Try this code:
CREATE TABLE t (period daterange);
INSERT INTO t VALUES ('[2014-09-26, 2014-09-28]'::daterange);
SELECT period,
lower(period),
upper(period),
daterange(lower(period), (upper(period)+INTERVAL '2 days')::date, '[]')
FROM t;
period | lower | upper | daterange
-------------------------+------------+------------+-------------------------
[2014-09-26,2014-09-29) | 2014-09-26 | 2014-09-29 | [2014-09-26,2014-10-02)
Note, that although I've specified 2014-09-28 to be a bounding upper value, unbounded 2014-09-29 is reported.
daterange