Skip to content

How to Select Current Week's Data in MySQL (1/3)

  • by
How to Select Current Month's Data in MySQL (3/3)
The following statement is trying to retrieve current week's data and will wrongly select extra data when using WEEK():
mysql> SELECT id, create_date, create_time FROM table_name WHERE WEEK(create_time) = WEEK(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time         |
+----+-------------+---------------------+
| 13 | 2013-07-09  | 2013-07-09 20:54:47 |
| 14 | 2013-07-09  | 2013-07-09 20:54:48 |
| 15 | 2013-07-09  | 2013-07-09 20:54:49 |
| 16 | 2013-07-09  | 2013-07-09 20:54:50 |
| 17 | 2013-07-09  | 2013-07-09 20:54:51 |
| 18 | 2014-07-10  | 2014-07-09 20:54:53 |
+----+-------------+---------------------+
6 rows in set (0.01 sec)

An extra row created in 2014 is wrongly selected in such statement. The correct way to do it is to use YEARWEEK() instead:
mysql> SELECT YEARWEEK(CURRENT_DATE);
+------------------------+
| YEARWEEK(CURRENT_DATE) |
+------------------------+
|                 201327 |
+------------------------+
1 row in set (0.00 sec)

Since YEARWEEK() returns information containing year and month. So, we can rewrite the statement into as following:
mysql> SELECT id, create_date, create_time FROM table_name WHERE YEARWEEK(create_time) = YEARWEEK(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time         |
+----+-------------+---------------------+
| 13 | 2013-07-09  | 2013-07-09 20:54:47 |
| 14 | 2013-07-09  | 2013-07-09 20:54:48 |
| 15 | 2013-07-09  | 2013-07-09 20:54:49 |
| 16 | 2013-07-09  | 2013-07-09 20:54:50 |
| 17 | 2013-07-09  | 2013-07-09 20:54:51 |
+----+-------------+---------------------+
5 rows in set (0.00 sec)

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

Leave a Reply

Your email address will not be published.