CODEWITHSUNDEEP
sqlmysql
willdanceforfun
willdanceforfun

Reputation: 11240

how do i get mysql results of todays date?

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

Upvotes: 1

Views: 4406

Answers (5)

Paul Chris Jones
Paul Chris Jones

Reputation: 2909

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

DATE(added) will remove the hours, minutes and seconds from your date, if they're there.

NOW() returns today's date and time.

DATE(NOW()) returns today's date.

So if you do WHERE DATE(added) = DATE(NOW()) then you will only get results where the date is today's date.

Upvotes: 0

Paul Dixon
Paul Dixon

Reputation: 300825

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

Upvotes: 2

longneck
longneck

Reputation: 12226

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.

Upvotes: 3

Jay
Jay

Reputation: 27464

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

Upvotes: 2

VolkerK
VolkerK

Reputation: 96159

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

Upvotes: 5

Related Questions