MySQL How to SELECT data from table which recorded today?

Question

Use PHP and MySQL. In my table, there is date field (datetime) recorded by NOW() sql function. Example value of data in this field is 2010-10-07 10:57:36. How can I SELECT all data which day-month-year is today. I try to use code as below:

  SELECT * FROM table WHERE date=????

Answer 1

Try this:

SELECT * FROM table WHERE date > CURDATE();

CURDATE() will return the current date as 2011-10-07 which will be cast to 2011-10-07 00:00:00 when comparing datetimes to it.

Note that if you use DATE(date) = CURDATE() you will run a date conversion for every row in the table, which will be really bad for your perfomance if you have many rows and/or you need to run the query often. Also make sure you have an index on date, otherwise both methods will be even slower.

Answer 2

SELECT * FROM table where DATE(date)=CURDATE()