MySQL greater than with microtime timestamp

Question

I have one PHP script inserting rows in a MySQL database. Each row has a field 'created_at' which is filled with the value of the PHP function microtime(true), and inserted as a double. (microtime because I need something more precise than to the second)

I have another PHP script that selects rows based on that created_at field.

When I go ahead and select like this: SELECT * FROM `ms_voltage` WHERE created_at > 1302775523.51878 I receive a resultset with, as the first row, the row with exactly that value for created_at.

This occurs from within my PHP script and from within PhpMyAdmin when manually doing the query. But not always, not for every value. Just once and a while really.

How is this possible? I didn't ask for greater than/equals, I want strictly greater than. Am I overlooking something type-related perhaps?

Yokoyokohama

An big thanks to all the (extremely quick) replies. I understand the problem much better now. I think I'm going to convert to an int to mitigate any rounding problems, that should certainly do the trick. Thanks again! – Yokoyokohama

Answer 1

Yeah, floating point arithmetic can do that sometimes. To understand why, it's helpful to realize that just as not all numbers can be accurately represented in base 10, not all numbers can be accurately represented in base 2 either.

For example, "1/3" may be written in base 10 as 0.33333 or 0.33334. Neither is really "correct"; they're just the best we can do. A "DOUBLE" in base 10 might be 0.3333333333 or 0.3333333334, which is double the digits, yet still not "correct".

The best options are to either use a DECIMAL value, or use an INT value (and multiply your actual values by, say, 10000 or 100000 in order to get the decimal digits you care about into that int).

Tereus

Answer 2

The DOUBLE type represent only approximate numeric data values. Try to use the DECIMAL type.

Mashburn

Answer 3

Is your column floating point? Calling microtime with true gives you a float, and that looks like a float, which will have digits after the .51878 that you don't see, so those digits make the stored value greater than the value you have in your query.

Unless you really need the float I'd convert the string result to an int, or even two columns for seconds and useconds. Then you can use > or < on known values without worrying about the imprecision of the floating point value.

Genie

Welcome to SO. I deleted your personal note, as that is generally frowned upon in the community. Nothing personal though. See also the faq. Cheers – Holden