Skip to content

How to Select Today's Data in MySQL (2/3)

There are various date functions to resolve different situations in MySQL. Here I introduce several ways to select today's data:

CURRENT_DATE Function

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)

TO_DAYS Function

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)

DATE_FORMAT Function

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.

Leave a Reply

Your email address will not be published. Required fields are marked *