Mark Stosberg (mark@summersault.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Date calculation produces wrong output with 7.02

Long Description
I use Postgres nearly every day and am very appreciative of the project. 

I think this example will my bug: 

[PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96]
cascade=> select date(CURRENT_DATE + ('30 days'::reltime));
      date
----------
9097-10-20

#############

It's quite likely my "date math" syntax is wrong, but it seems that Postgres should either return the right result, or let me know something is fault. 

Sample Code


No file was uploaded with this report


Fri, 23 Feb 2001 12:18:15 -0500 (EST)
pgsql-bugs@postgresql.org
Date calculation produces wrong output with 7.02
Mark Stosberg (mark@summersault.com) writes:

> [PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96]
> cascade=> select date(CURRENT_DATE + ('30 days'::reltime));
>       date
> ----------
> 9097-10-20

Ugh.  What is happening here is that there is no '+' operator between
types date and reltime, but there is one between date and int4 (with
behavior of adding that many days to the date).  And reltime is
considered binary-compatible with int4, so you get

select date(CURRENT_DATE + ('30 days'::reltime)::int4);

Now '30 days'::reltime::int4 yields 2592000, so you get a silly final
result.

The correct query for Mark is

	select date(CURRENT_DATE + ('30 days'::interval));

but I wonder whether the binary equivalence between reltime and int4
might not be ill-advised.  Thomas, any thoughts here?

			regards, tom lane

Fri, 23 Feb 2001 12:40:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date calculation produces wrong output with 7.02 
> Date calculation produces wrong output with 7.02
> cascade=> select date(CURRENT_DATE + ('30 days'::reltime));
>       date
> ----------
> 9097-10-20
> It's quite likely my "date math" syntax is wrong, but it seems
> that Postgres should either return the right result, or let me
> know something is fault.

Your syntax is right, and Postgres is wrong :(

The problem is that there is no explicit date+reltime math operator.
But, there *is* a date+int operator which assumes the int is in days,
and there *is* a "binary compatible" entry for reltime->int and vica
versa.

So, Postgres is actually doing

  select date(CURRENT_DATE + int('30 days'::reltime));

but the units are "seconds" coming from reltime, and the subsequent math
assumes it was "days".

You can work around the problem with

  select date(CURRENT_DATE + interval('30 days'::reltime));

or with

  select date(CURRENT_DATE + '30 days'::reltime/86400);

This problem is in the current CVS tree also. A workaround of removing
the reltime==int assumed compatibility could be applied to 7.1 (I
haven't thought of what that would affect) or we can build some explicit
operators to make sure that the seconds->days conversion happens (which
would require an initdb).

btw, "interval" is to be preferred over "reltime" for most operations,
as recommended in the PostgreSQL docs on data types.

Comments?

                        - Thomas

Fri, 23 Feb 2001 17:48:45 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Date calculation produces wrong output with 7.02
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> This problem is in the current CVS tree also. A workaround of removing
> the reltime==int assumed compatibility could be applied to 7.1 (I
> haven't thought of what that would affect) or we can build some explicit
> operators to make sure that the seconds->days conversion happens (which
> would require an initdb).
> btw, "interval" is to be preferred over "reltime" for most operations,
> as recommended in the PostgreSQL docs on data types.

Removing the binary compatibility was my thought also.  If we are trying
to discourage use of reltime, then this seems like a good change to
make...

			regards, tom lane

Fri, 23 Feb 2001 13:36:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date calculation produces wrong output with 7.02 