Thread

1. Date calculation produces wrong output with 7.02

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-23T17:18:15Z

   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
   
   
   

2. Re: Date calculation produces wrong output with 7.02

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-23T17:40:40Z

   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
   
   

3. Re: Date calculation produces wrong output with 7.02

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-02-23T17:48:45Z

   > 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
   
   

4. Re: Date calculation produces wrong output with 7.02

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-23T18:36:19Z

   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