- Use CURRENT_DATE function.
- Use TO_DAYS function to convert date or timestamp to an absolute number.
- Use DATE_FORMAT to get date strings and compare the two.
mysql> SELECT id, create_date, create_time FROM table_name WHERE create_date = CURRENT_DATE;
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 8 | 2013-07-09 | 2013-07-09 19:19:07 |
| 9 | 2013-07-09 | 2013-07-09 19:19:18 |
| 10 | 2013-07-09 | 2013-07-09 19:19:21 |
| 11 | 2013-07-09 | 2013-07-09 19:19:27 |
| 12 | 2013-07-09 | 2013-07-09 19:19:32 |
+----+-------------+---------------------+
5 rows in set (0.00 sec)
But, if the data type of this column is timestamp, you should add a function DATE() to the data:
mysql> SELECT id, create_date, create_time FROM table_name WHERE DATE(create_time) = CURRENT_DATE;
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 8 | 2013-07-09 | 2013-07-09 19:19:07 |
| 9 | 2013-07-09 | 2013-07-09 19:19:18 |
| 10 | 2013-07-09 | 2013-07-09 19:19:21 |
| 11 | 2013-07-09 | 2013-07-09 19:19:27 |
| 12 | 2013-07-09 | 2013-07-09 19:19:32 |
+----+-------------+---------------------+
5 rows in set (0.00 sec)
mysql> SELECT id, create_date, create_time FROM table_name WHERE TO_DAYS(create_time) = TO_DAYS(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 8 | 2013-07-09 | 2013-07-09 19:19:07 |
| 9 | 2013-07-09 | 2013-07-09 19:19:18 |
| 10 | 2013-07-09 | 2013-07-09 19:19:21 |
| 11 | 2013-07-09 | 2013-07-09 19:19:27 |
| 12 | 2013-07-09 | 2013-07-09 19:19:32 |
+----+-------------+---------------------+
5 rows in set (0.00 sec)
mysql> SELECT id, create_date, create_time FROM table_name WHERE DATE_FORMAT(create_time,'%Y%m%d') = DATE_FORMAT(CURRENT_DATE,'%Y%m%d');
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 8 | 2013-07-09 | 2013-07-09 19:14:07 |
| 9 | 2013-07-09 | 2013-07-09 19:14:18 |
| 10 | 2013-07-09 | 2013-07-09 19:14:21 |
| 11 | 2013-07-09 | 2013-07-09 19:14:27 |
| 12 | 2013-07-09 | 2013-07-09 19:14:32 |
+----+-------------+---------------------+
5 rows in set (0.02 sec)
Using this function can make your SQL statement very flexible in various kind of situation when retrieving data, but it costs database performance when the table is big enough.