Thread: Date calculation produces wrong output with 7.02

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
letme know something is fault.  

Sample Code


No file was uploaded with this report

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

> 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

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