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:
mysqlselect 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)
weee :)
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
-
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
