Skip to content

How to Select Current Week's Data in MySQL (1/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)

Leave a Reply

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