Skip site navigation (1) Skip section navigation (2)

Re: Date calculation produces wrong output with 7.02

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: mark(at)summersault(dot)com, pgsql-bugs(at)postgresql(dot)org
Cc: Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Date calculation produces wrong output with 7.02
Date: 2001-02-23 17:48:45
Message-ID: 3A96A27D.B24B44A4@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
> 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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-02-23 17:51:00
Subject: CURRENT_DATE and CURRENT_TIME are broken
Previous:From: Tom LaneDate: 2001-02-23 17:40:40
Subject: Re: Date calculation produces wrong output with 7.02

pgsql-bugs by date

Next:From: Thomas LockhartDate: 2001-02-23 17:53:23
Subject: Re: Turkish locale bug
Previous:From: Tom LaneDate: 2001-02-23 17:40:40
Subject: Re: Date calculation produces wrong output with 7.02

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group