Hello again,
In reply to all the suggestions made, here it goes one more mail. I hope it
will be helpful.
(André, isto vai em ingles que é para o pessoal perceber ;)
Suggestion 1:
>Float aren't really searchable in Mysql. You may need to do some casting,
>not sure how to do it on top but i remember reading from the Mysql help.
Couldn't find out how :(((
Suggestion 2:
>You forgot "" around temp example:
>mysql>select datetime,temperatura from temperatura where
>datetime="2002-02-08 09:30:00" and temperatura="15.8";
This does not work (I had tried this before of course). The output is an
empty set :(
Suggestion 3:
>Replace the dot for a comma.
Wrongo... it does not work:
mysql> select datetime,temperatura from temperatura where
datetime="2002-02-08 09:30:00" and temperatura=15,8;
ERROR 1064: You have an error in your SQL syntax near '8' at line 1
or (to clear everything up)
mysql> select datetime,temperatura from temperatura where
datetime="2002-02-08 09:30:00" and temperatura="15,8";
Empty set (0.00 sec)
Suggestion 4:
>15.8 has no exact binary representation, so a test for equality will fail.
>Do not use floating point if you want to test for equality. Try DECIMAL
Miguel, RTFM ;)
"DECIMAL and NUMERIC values are stored as string rather than a binary
floating-point numbers, in order to preserve the decimal precision of those
values." - MySQL Reference Manual 3.23.39 page 177.
mysql> create table temp_test (datetime datetime, temperatura DECIMAL(2,1));
mysql> insert into temp_test(datetime,temperatura) values ("2002-02-08
09:30:00",15.8);
mysql> select datetime,temperatura from temp_test where datetime="2002-02-08
09:30:00" and temperatura=15.8;
+---------------------+-------------+
| datetime | temperatura |
+---------------------+-------------+
| 2002-02-08 09:30:00 | 15.8 |
+---------------------+-------------+
1 row in set (0.00 sec)
weeeeeeeeeeeeeeeeeee :)
Suggestion 5:
>This is not a MySQL problem, nor a database one, but a general computer
>science one. It is
>almost never save to compare two floating point numbers for equality. The
>decimal representation
>you see in ASCII is not an exact representation but an approximation.
>Because of the
>way computers store floating point numbers in bunry and humans read them in
>decimal,
>the decimal value is alway rounded and there are invisible bits which can
>be different
>in apparently identical values. For floating point comparisons you should
>*always* use
>unequalities e.g. "WHERE temp BETWEEN 15.75 AND 15.85".
mysql> select datetime,temperatura from temperatura where
datetime="2002-02-08 09:30:00" and temperatura between 15.75 and 15.85;
+---------------------+-------------+
| datetime | temperatura |
+---------------------+-------------+
| 2002-02-08 09:30:00 | 15.8 |
+---------------------+-------------+
1 row in set (0.00 sec)
Works :))))
Conclusion:
I think I will follow suggestion 4, and alter my table.
Thank for all the help guys :)))))
Best wishes,
Miguel
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
