Thread: BUG #1179: Time comparison fail
The following bug has been logged online:
Bug reference: 1179
Logged by: Mauricio CASTRO ESPINOSA
Email address: mcastroe@hotmail.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,starteddatetime)))*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
On Tue, 22 Jun 2004, PostgreSQL Bugs List wrote: > must_betrue is false for the query bellow 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?
On Tue, 22 Jun 2004, Mauricio CASTRO wrote: > You are right! > > I am doing something wrong? I understand that there is a difference but I > don't know why. 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.
You are right!
I am doing something wrong? I understand that there is a difference but I
don't know why.
mex2=# select
mex2-# age(endeddatetime,starteddatetime),
mex2-# time,
mex2-# date_part('second',(age(endeddatetime,starteddatetime)))*1000,
mex2-# date_part('second',(age(endeddatetime,starteddatetime)))*1000-time
as Must_be_Zero,
mex2-# date_part('second',((starteddatetime)))*1000 as starteddatetime_s,
mex2-# date_part('second',((endeddatetime)))*1000 as endeddatetime_s,
mex2-# starteddatetime,
mex2-# endeddatetime
mex2-# from entry where not
date_part('second',(age(endeddatetime,starteddatetime)))*1000=time;
age | time | ?column? | must_be_zero | starteddatetime_s
| endeddatetime_s | starteddatetime | endeddatetime
--------------+------+----------+-----------------------+-------------------+-----------------+----------------------------+----------------------------
00:00:04.031 | 4031 | 4031 | -4.54747350886464e-13 | 51171 |
55202 | 2004-05-10 12:47:51.171-05 | 2004-05-10 12:47:55.202-05
00:00:04.094 | 4094 | 4094 | 4.54747350886464e-13 | 51171 |
55265 | 2004-05-10 12:47:51.171-05 | 2004-05-10 12:47:55.265-05
00:00:04.047 | 4047 | 4047 | -4.54747350886464e-13 | 32781 |
36828 | 2004-05-10 12:50:32.781-05 | 2004-05-10 12:50:36.828-05
00:00:02.047 | 2047 | 2047 | 2.27373675443232e-13 | 54171 |
56218 | 2004-05-10 14:10:54.171-05 | 2004-05-10 14:10:56.218-05
00:00:04.046 | 4046 | 4046 | 4.54747350886464e-13 | 5312 |
9358 | 2004-05-10 14:13:05.312-05 | 2004-05-10 14:13:09.358-05
(5 rows)
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail