If you’re selecting by date only, base your calculations on CURDATE (which returns date only) rather than NOW (which returns date and time). These examples will catch all times within the day ranges:
- Today:
WHERE timestamp >= CURDATE() - Yesterday:
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE() - This month:
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY) - Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June):
WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'
The „this week“ depends on which day you start your week; I’ll leave that to you. You can use the DAYOFWEEK function to tweak CURDATE() to the proper ranges.
Addendum: OP’s column type was INTEGER, storing a UNIX timestamp, and my answer assumed the column type was TIMESTAMP. Here’s how to do all the same things with a UNIX timestamp value and still maintain optimization if the column is indexed (as the answers above will do if the TIMESTAMP column is indexed)…
Basically, the solution is to just wrap the beginning and/or ending dates in the UNIX_TIMESTAMP function:
- Today:
WHERE timestamp >= UNIX_TIMESTAMP(CURDATE()) - Yesterday:
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE()) - This month:
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)) - Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June):
WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')