Skip to content
MySQL

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

  • by
How to Select Current Week's Data in MySQL (1/3)
There are various date functions to resolve different situations in MySQL. Here I introduce several ways to select today's data:
  1. Use CURRENT_DATE function.
    2. 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)
  2. Use TO_DAYS function to convert date or timestamp to an absolute number.
    3. 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)
  3. Use DATE_FORMAT to get date strings and compare the two.
    4. 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.
How to Select Current Month's Data in MySQL (3/3)
Tags:

Leave a Reply

Your email address will not be published.