I’d like to chime in here.
- In MySQL, if you apply a function to a column value in the WHERE clause it cannot use the index for that query.
- In MySQL, if you apply a function to a column value in the WHERE clause it cannot use the index for that query.
Indeed, that the same point twice. For emphasis don’t you know.
So this …
select orderDate
from tbl
where orderDate between CURRENT_DATE and DATE( CURRENT_DATE + 6 ) --or +7 or +8, whatever
order by orderDate asc
… forces MySQL to do a full table scan. On a table with 500k rows. Might not be the best idea in the world.
And unlike Oracle, MySQL does not have function based indexes, so that’s not an option either.
So, if life gives you lemons, make lemonade. Or if life gives you timestamps, make timestamp’onade. Or something.
Okay, so the difference between 7 and 8
1st 12:00 2nd 12:00 3rd 12:00 4th 12:00 5th 12:00 6th 12:00 7th 12:00 8th 12:00 9th 12:00
.............1............2............3............4............5............6............7????????????
1st 12:00 2nd 12:00 3rd 12:00 4th 12:00 5th 12:00 6th 12:00 7th 12:00 8th 12:00 9th 12:00
.............1............2............3............4............5............6............7............8????????
Since we’re using timestamps, and thus date and time, we can start at any point of the day. Suppose we start the 1st at 12:00 (24h notation). Then if we add 7 days we end up 12:00 on the 8th. So we miss all the orders beween 12:00 and 0:00 on the 9th. i.e., what we’re showing the user of the system is not complete.
If however we change the 7 to 8 we get too much information, that is, we also get from 0:00 until 12:00 on the 9th. But that’s alright since the beauty of too much information is that you can ignore the part that you don’t like.
So first you request the data for 8 days from MySQL, and then in PHP you ignore the data from that part of the last day you don’t actually need.
That being said, I wonder if the OP couldn’t just use
// PHP <= 5.3
$start = mktime(0, 0, 0); // timestamp for today 0:00
$end = mktime(0, 0, 0, date('n'), date('j') + 7, date('Y')); // timestamp of the date 7 days from now, 0:00
$query = "SELECT order, data, stuff FROM daTable where orderdate >= $start and orderdate <= $end";
// PHP >= 5.3 alternative:
$start = new \\DateTime()->modify('midnight')->getTimestamp();
$end = new \\DateTime()->modify('+7 day, midnight')->getTimestamp();
$query = "SELECT order, data, stuff FROM daTable where orderdate >= $start and orderdate <= $end";
// \\DateTime rocks!
That should take care of the time problem just nicely.