The following bug has been logged online:

Bug reference: 1179
Logged by: Mauricio CASTRO ESPINOSA

Email address: mcastroe (AT) hotmail (DOT) com

PostgreSQL version: 7.2.4

Operating system: RedHat 7.3 uname:Linux Play 2.4.18-3 #1 Thu Apr 18
07:37:53 EDT 2002 i686 unknown

Description: Time comparison fail

Details:

must_betrue is false for the query bellow


psql:
----------------------------
age(endeddatetime,starteddatetime),time,date_part( 'second',(age(endeddateti
me,starteddatetime)))*1000,date_part('second',(age (endeddatetime,starteddat
mex2=# select etime)))*1000=time as
Must_beTrue,starteddatetime,endeddatetime from entry where not
date_part('second',(age(endeddatetime,starteddatet ime)))*1000=time;
age | time | ?column? | must_betrue | starteddatetime |
endeddatetime
--------------+------+----------+-------------+----------------------------
+----------------------------
00:00:04.031 | 4031 | 4031 | f | 2004-05-10 12:47:51.171-05 |
2004-05-10 12:47:55.202-05
00:00:04.094 | 4094 | 4094 | f | 2004-05-10 12:47:51.171-05 |
2004-05-10 12:47:55.265-05
00:00:04.047 | 4047 | 4047 | f | 2004-05-10 12:50:32.781-05 |
2004-05-10 12:50:36.828-05
00:00:02.047 | 2047 | 2047 | f | 2004-05-10 14:10:54.171-05 |
2004-05-10 14:10:56.218-05
00:00:04.046 | 4046 | 4046 | f | 2004-05-10 14:13:05.312-05 |
2004-05-10 14:13:09.358-05
(5 rows)


mex2=# select count(*) from entry;
count
-------
689
(1 row)

mex2=# \d entry
Table "entry"
Column | Type | Modifiers
-------------------+--------------------------+----------------------------
----------------
id | integer | not null default
nextval('entry_id'::text)
ord | integer |
func | character varying(200) |
source | character varying(200) |
started | bigint |
starteddatetime | timestamp with time zone |
time | bigint |
size | bigint |
operation | character varying(200) |
result | character varying(200) |
type | character varying(200) |
url | character varying(1500) |
authority | character varying(200) |
path | character varying(750) |
query | character varying(750) |
incache | boolean |
lastmodificaction | timestamp with time zone |
expires | timestamp with time zone |
lastcacheupdate | timestamp with time zone |
lastaccess | timestamp with time zone |
endeddatetime | timestamp with time zone |
Primary key: entry_pkey

mex2=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

mex2=# \!uname -a
Linux Play 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Tue, 22 Jun 2004, PostgreSQL Bugs List wrote:

One of those is a bigint, the other is a double expression that is
probably very slightly different from the integer you're comparing it to.
What is the result of the date_part(...)-time?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Tue, 22 Jun 2004, Mauricio CASTRO wrote:

Floating point math is inexact. The date_part(...) is returning a value
that is very close to, but not exactly 4.031 due to accuracy limitations.
So, when you multiply it by 1000 you can get a value very close to, but
not exactly 4031. When it tries to compare it to the bigint 4031, it
converts the bigint to a double as well and gets a different double from
the first.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly