Re: Bug #501: plpgsql, date data type and time change

pgsql-bugs(at)postgresql(dot)org writes:
> --Incrmenter la date de 1 jour
> select date(dateJour + ''1 day''::interval) into dateJour;

This is a bad way to increment a date. You're implicitly converting
the date to timestamp and doing a timestamp + interval addition to
yield a timestamp, which is then truncated back to date. Works fine
except on daylight-savings transition days, because '1 day'::interval
actually means 24 hours:

regression=# select '2001-10-28'::date::timestamp;
timestamptz
------------------------
2001-10-28 00:00:00-04
(1 row)

regression=# select '2001-10-28'::date + '1 day'::interval;
?column?
------------------------
2001-10-28 23:00:00-05
(1 row)

regression=# select date('2001-10-28'::date + '1 day'::interval);
date
------------
2001-10-28
(1 row)

Instead, use plain date addition (date plus an integer):

regression=# select '2001-10-28'::date + 1;
?column?
------------
2001-10-29
(1 row)

Should be a tad faster by saving datatype conversions, as well as
correct.

I have suggested in the past that type interval needs to consider
"1 day" and "24 hours" to be distinct concepts, just as "1 month"
and "1 year" are not equivalent to any fixed number of days. But
I haven't gotten much traction on the issue; it doesn't help that
this bug is wired into the SQL spec's definition of interval :-(

regards, tom lane