Rick Mason ([EMAIL PROTECTED]) 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
