Date range query behaviour on different MySQL versions

Hey guys,

Just wondering if anybody could shed some light on this odd behaviour.
Take the following statement (I’ve trimmed out a chunk for clarity)

SELECT * FROM orders WHERE order_date >= '2010-11-23 00:00:00' AND order_date <= '2010-11-23 23:59:59' ORDER BY orders.order_date DESC

Now on my Mac dev. machine running MySQL v.5.1.49, this query returns NO results. Yet there are definitely rows that should be returned.

On the production server, running MySQL v.5.1.54, this query returns all appropriate rows.

I can only recreate this problem when the the date span is within the same day.


Well, turns out this has nothing to do with MySQL.

I’d added a line in one of my classes where I fetch data

while($row = mysqli_fetch_assoc($result)) $this->grand_total += $row['total'];

Then further on, I fetch the data again but the result pointer is at the end due to the previous fetch therefore returning 0 rows. You need to reset like so:

mysqli_data_seek($result,0); //Reset the result pointer