MYSQL select where date within this day

Question

MY query looks like this:

SELECT COUNT(entryID) 
FROM table 
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Will this count the rows whose date values are within the day (starting at 12:00; not within 24 hours)? If not, how do I do so?

Thomson

I think my solution is WHERE DAYNAME(date) == CURDATE(). The problem is that DAYNAME returns word form 'Friday'. I need number form so that it can compare with CURDATE(). – Thomson

It will return all records for yesterday, starting from the beginning of the day, not only those within 24 hours of when it was run. – Elegiac

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) returns 2012-03-29, so >= '2012-03-29 is all records from yesterday and today. – Elegiac

If you only want the previous 24 hours, use NOW() instead of CURDATE() – Elegiac

Answer 1

The following should be enough to get records within the current day:

SELECT COUNT(entryID) 
FROM table 
WHERE date >= CURDATE()

As Michael notes in the comments, it looks at all records within the last two days in its current form.

The >= operator is only necessary if date is actually a datetime - if it's just a date type, = should suffice.

Displeasure

Thanks. You wrote the solution as I was writing my own solution. (I use DATE(date) to get just the date part, though). – Thomson

@Thomson - Cool! Just using = should be enough then, and actually more correct in case there were ever future dates in there. – Displeasure

I just remembered now that using DATE(date) will stop indexes on row date from working. Would it work for me to use WHERE date >= CURDATE() even when date is DATETIME? Will it still only get rows from the very beginning of the day? – Thomson

@Thomson - It should still work for you. I actually wrote the answer thinking date might be datetime. – Displeasure

Answer 2

Here's the solution:

SELECT COUNT(entryID) FROM table WHERE DATE(date) >= CURDATE()

Since my date column is type DATETIME, I use DATE(date) to just get the date part, not the time part.

Thomson

This solution is very bad performance wise, it have to apply the function to every row in the table and compare after to the current date. With a big table it will arise performance problems. – Scarborough

Answer 3

SELECT DATE_FORMAT(NOW(), "%Y-%m-%d 00:00:00");

Output today's start time

WHERE date >= DATE_FORMAT(NOW(), "%Y-%m-%d 00:00:00")

Sometimes

Answer 4

CURDATE() returns a date like '2012-03-30', not a timestamp like '2012-03-30 21:38:17'. The subtraction of one day also returns just a date, not a timestamp. If you want to think of a date as a timestamp think of it as the beginning of that day, meaning a time of '00:00:00'.

And this is the reason, why this

WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

and this

WHERE date > CURDATE()

do the same.

I have another hint: SELECT COUNT(entryID) and SELECT COUNT(*) give the same result. SELECT COUNT(*) gives the database-machine more posibilities to optimize counting, so COUNT(*) is often (not always) faster than COUNT(field).

Susannahsusanne

Good answer - I think you have the operators mixed up in your example though. – Displeasure

Wouldn't SELECT COUNT(*) be slower since * means all rows? – Thomson

Actually with INNODB a COUNT(primary_id) is faster. COUNT(*) can be very slow in a large INNODB table. – Campeche