CODEWITHSUNDEEP
mysqldate
Hope4You
Hope4You

Reputation: 1947

MYSQL select where date within this day

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?

Upvotes: 14

Views: 24627

Answers (4)

Akbarali
Akbarali

Reputation: 904

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")

Upvotes: 2

Hubert Schölnast
Hubert Schölnast

Reputation: 8497

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).

Upvotes: 1

Hope4You
Hope4You

Reputation: 1947

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.

Upvotes: 2

Paul Bellora
Paul Bellora

Reputation: 55213

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.

Upvotes: 20

Related Questions