how do i get mysql results of todays date?

Question

I'm trying to pull results from a table that have been inserted today. Each insert is marked with a timestamp.

If I do a query like:

SELECT * 
  FROM table 
  WHERE added > DATE_SUB(NOW(), INTERVAL 1 DAY)

It seems I get stuff that did fall on today's date, but also stuff from 24 hours ago. maybe I'm seeing things.

I don't want to see stuff from 24 hours ago...

Answer 1

You can extract the date part of the timestamp field and compare it against the current date.

SELECT x,y,z FROM t WHERE Date(added)=Curdate()

Answer 2

you can use the trick that mysql compares a date as being at midnight when compared to a datetime value:

SELECT * FROM table WHERE added >= CURRENT_DATE

this has the advantage that it can use an index.

Answer 3

"interval 1 day" does not mean "the same calendar day", it means "within 24 hours". There are many situations where this is useful. Like, you might want to say, Show me all the responses to my posts made within the last 24 hours. If I ran such a query when I arrived at work in the morning, it would tell me all the responses that came in since I asked yesterday morning, plus those that came in this morning before I arrived. That would likely be what I want to know.

Apparently it's not useful in your case, so ... use something else! Like the other posters have suggested.

Answer 4

I tend to use TO_DAYS() to turn times into a day number, e.g.

SELECT * 
  FROM table 
  WHERE TO_DAYS(added)=TO_DAYS(NOW())