From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug #484: TIMESTAMP arithmetic insconsistencies |
Date: | 2001-10-17 04:13:54 |
Message-ID: | 200110170413.f9H4Ds992784@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2001-10-17 05:19:46 | Bug #485: strange behavior when creting rules with serial id |
Previous Message | Tom Lane | 2001-10-16 23:41:48 | Re: psql core dump |