Обсуждение: Bug #733: Date Arithmetics within plsql
Joerg Wedeck (joerg.wedeck@datacare.de) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Date Arithmetics within plsql
Long Description
Hi,
i have a problem with date arithmetics in plpgsql which i was able to track down into one simple function:
create function inc_date (date, interval) returns date as '
declare
help date;
begin
help := $1 + $2;
return help;
end;
' language 'plpgsql';
the following is the result:
jw=# select inc_date (date '2002-10-25', interval '1 day');
inc_date
------------
2002-10-26 -> ok
(1 row)
jw=# select inc_date (date '2002-10-25', interval '2 days');
inc_date
------------
2002-10-27 -> ok
(1 row)
jw=# select inc_date (date '2002-10-25', interval '3 days');
inc_date
------------
2002-10-27 -> oops ???? i would expect 2002-10-28
(1 row)
jw=# select inc_date (date '2002-10-25', interval '4 days');
inc_date
------------
2002-10-28
(1 row)
jw=# select inc_date (date '2002-10-25', interval '5 days');
inc_date
------------
2002-10-29
(1 row)
it happens only around the 27 th of october this year, on 26th next year ...
Version:
jw=# SELECT version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
jw=#
Installation:
package postgresql 7.2.1-2
debian/woody (same behavior on suse 8.0, redhat 7.3)
thank you, joerg
Sample Code
No file was uploaded with this report
There is an extra hour on Oct 27th. We go from daylight saving time back to standard time... pgsql-bugs@postgresql.org wrote: > > Joerg Wedeck (joerg.wedeck@datacare.de) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Date Arithmetics within plsql > > Long Description > Hi, > > i have a problem with date arithmetics in plpgsql which i was able to track down into one simple function: > > create function inc_date (date, interval) returns date as ' > declare > help date; > begin > help := $1 + $2; > return help; > end; > ' language 'plpgsql'; > > the following is the result: > > jw=# select inc_date (date '2002-10-25', interval '1 day'); > inc_date > ------------ > 2002-10-26 -> ok > (1 row) > > jw=# select inc_date (date '2002-10-25', interval '2 days'); > inc_date > ------------ > 2002-10-27 -> ok > (1 row) > > jw=# select inc_date (date '2002-10-25', interval '3 days'); > inc_date > ------------ > 2002-10-27 -> oops ???? i would expect 2002-10-28 > (1 row) > > jw=# select inc_date (date '2002-10-25', interval '4 days'); > inc_date > ------------ > 2002-10-28 > (1 row) > > jw=# select inc_date (date '2002-10-25', interval '5 days'); > inc_date > ------------ > 2002-10-29 > (1 row) > > it happens only around the 27 th of october this year, on 26th next year ... > > Version: > jw=# SELECT version(); > version > --------------------------------------------------------------- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > (1 row) > > jw=# > > Installation: > package postgresql 7.2.1-2 > debian/woody (same behavior on suse 8.0, redhat 7.3) > > thank you, joerg > > Sample Code > > No file was uploaded with this report > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
pgsql-bugs@postgresql.org writes:
> jw=# select inc_date (date '2002-10-25', interval '1 day');
> inc_date
> ------------
> 2002-10-26 -> ok
> (1 row)
What you probably want here is the date plus integer operator.
There is no date plus interval operator --- what you are actually
getting is implicit promotion of date to timestamp, then timestamp
plus interval, then coercion back to date. Unfortunately that's
going to create roundoff problems when you cross daylight-savings
boundaries.
regards, tom lane