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

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