mw@hesotech.de writes:
> When I execute the statement
> select timestamptz '2012-03-01 00:00:00+00' + interval '1 month'
> I expect the result:
> "2012-04-01 02:00:00+02"
> but postgres returns:
> "2012-04-01 01:00:00+02"
This does not look like a bug to me. You did not say what time zone you
are using, but I bet it is one that is UTC+1 in the winter and switches
to UTC+2 during March. For instance, if I try this in Europe/Amsterdam
zone I get:
regression=# set timezone = 'Europe/Amsterdam';
SET
regression=# select now();
now
-------------------------------
2012-05-22 01:04:52.556207+02
(1 row)
regression=# select timestamptz '2012-03-01 00:00:00+00';
timestamptz
------------------------
2012-03-01 01:00:00+01
(1 row)
The above is correct since midnight UTC corresponds to 1AM Amsterdam
winter time ...
regression=# select timestamptz '2012-03-01 00:00:00+00' + interval '1 month';
?column?
------------------------
2012-04-01 01:00:00+02
(1 row)
... and adding '1 month' to a timestamptz is defined to produce the same
local time, so this is the correct result.
If you don't want such behavior, you could use a timezone setting that
has no DST transitions. Or possibly you want to do the arithmetic with
the type timestamp without time zone, rather than with time zone.
Or you could express the interval to be added as so many
hours/minutes/seconds, rather than using the variable-size units of
days/weeks/months/years.
regards, tom lane
