MYSQL select where the date is on this day

My request looks like this:

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

      

Will this count rows whose values date

are throughout the day (starting at 12:00, not over 24 hours)? If not, how do I go about it?

+3


source to share


3 answers


To get records for the current day, it should be enough:

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

      



As Michael points out in the comments, he reviews all entries from the last two days in his current form.

The operator >=

is only needed if, date

in fact, a datetime

- if it is just a type date

, =

should suffice.

+8


source


Here's the solution:

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



Since my column date

is of type DATETIME

, I am using DATE(date)

to just get the date part, not the time part.

+2


source


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

And that's why, why this

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

      

and this one

WHERE date > CURDATE()

      

do the same thing.

I have another hint: SELECT COUNT(entryID)

u SELECT COUNT(*)

give the same result. SELECT COUNT(*)

gives the database machine more room to optimize the count, so it is COUNT(*)

often (not always) faster than COUNT(field)

.

+1


source







All Articles