Check only date from datetime field

Question

I have a MySql database that contains the date and time in one field in this format:

'2011-01-04 16:32:49' 

How can I filter the results based on this datetime so that it only shows results with Today. basically, I want to compare only with the date of part of the field.

Can I do something like this?

 Select * from table where timestamp.date() = now().date 

Answer 1

Use DATE :

 SELECT * FROM table WHERE DATE(timestamp) = DATE(now()) 

Another alternative that will be able to use the index on timestamp , if you have one:

 SELECT * FROM table WHERE timestamp >= DATE(now()) AND timestamp < DATE(now()) + interval 1 day 

Answer 2

Yes, you can, but the column that stores the dateTime field in your database just needs to be referenced, you should not call the .date () function on it.

Answer 3

For mySQL, it will be:

Select * from the table where DATEDIFF (NOW (), timestamp) = 0

This will only give you entries in which Date Date.

Answer 4

  Select * from table where DATE_FORMAT(timestamp, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')