1

I am developing a chat module with JavaScript and php+mysql and I am storing the chat data into a simple table with: id, member1, member2, message, timestamp. The timestamp column is double(14,4), where I store the timestamp on microseconds level, ex: 1330522898.3137;

My problem is: When I am comparing this timestamp with another one, like

   SELECT * FROM chat_rows WHERE timestamp >= 1330535168.9548

Only on particular numbers, like this one above, I get 0 rows using operator >= although I have a row with exact same value; This is proved by the almost same query differentiated by operator = :

   SELECT * FROM chat_rows WHERE timestamp = 1330535168.9548

which returns one row;

The workaround for this is to replace the double(14,4) type with varchar but is slower than double;

Also fails only on this server ( my development one ): Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.58

On my local it seems that works; - which is windows Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/0.9.8o Versiunea clientului MySQL: mysqlnd 5.0.8-dev - 20102224 - $Revision: 318113 $

Any thoughts ?

Thanks in advance!

Serbu Florin-Adrian
  • 511
  • 5
  • 14
  • 2
    Can't you use `decimal(14,4)`? – a1ex07 Feb 29 '12 at 17:22
  • If you cast it to a string, then compare the strings, it should work. The problem is that the 14,4 is the display size, but the value and the comparison go beyond that. The alternative is to use decimal. – Marcus Adams Feb 29 '12 at 17:49
  • Thank you for your help, it really worked; I still did not understood why this is happening, if I have 14,4 double values stored and I am comparing to the same number, why should the comparison go beyond ? – Serbu Florin-Adrian Mar 01 '12 at 07:48

1 Answers1

2

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value.

MySQL floating point comparison issues

Community
  • 1
  • 1
scibuff
  • 13,377
  • 2
  • 27
  • 30