Bug #484: TIMESTAMP arithmetic insconsistencies

Rick Mason (mp(at)grymmjack(dot)com) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
TIMESTAMP arithmetic insconsistencies

Long Description
I have found some inconsistencies relating to TIMESTAMP arithmetic. I am not sure if this is a bug, but perhaps someone can give me a hint as to what is happening.

I have verified my results on these two systems:
PostgreSQL 7.1.2 on i386--freebsd4.3, compiled by GCC 2.95.3
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

(any differences in version are noted below)

First try these queries:

select now()-'2001-09-30';
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < 50.0;
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < '50 days';
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < 50;
> returned 0 rows

select now()-'2005-09-30';
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < 50.0;
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < '50 days';
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < 50;
> "-1444 days -xx:xx:xx"

Now is where things get a little wierd, create a table such as:
CREATE TABLE tryme (invoice_date TIMESTAMP);
now fill the table with alot of dates, including some in the past and some in the future
(my source data is ~1000 rows, 99% of which have 00:00:00 in the time part of the time field)

select now()-invoice_date from tryme order by (now()-invoice_date);
> returns all intervals (positive and negative)

select now()-invoice_date from tryme where (now()-invoice_date)<50 order by (now()-invoice_date);
> returns all negative intervals only

select now()-invoice_date from tryme where (now()-invoice_date)<50. order by (now()-invoice_date);
> returns intervals (<50 days OR >100 days) (including negatives)

select now()-invoice_date from tryme where (now()-invoice_date)<60. order by (now()-invoice_date);
> returns intervals (<60 days OR >100 days) (including negatives)

select now()-invoice_date from tryme where (now()-invoice_date)<120. order by (now()-invoice_date);
>7.1.2 returns intervals (>100 days AND <120 days) (including negatives)
>7.1.3 returns intervals (>100 days AND <120 days) (excluding negatives)

select now()-invoice_date from tryme where (now()-invoice_date)>50 order by (now()-invoice_date);
> returns all positive intervals only

select now()-invoice_date from tryme where (now()-invoice_date)>50. order by (now()-invoice_date);
> returns intervals ((>50 days AND < 100 days) OR >500 days)

select now()-invoice_date from tryme where (now()-invoice_date)>120. order by (now()-invoice_date);
>7.1.2 returns intervals (<100 days OR >120 days) (excluding negatives)
>7.1.3 returns intervals (<100 days OR >120 days) (including negatives)

Now if you replace the numbers with an interval string (example: 50 to '50 days') then all of the queries work fine.

Sample Code

No file was uploaded with this report