How to Query MySQL for Records Within the Current Week?

Querying MySQL for Records within the Current Week

Determining records from the current week in a MySQL database can be achieved through various approaches. One proposed method involves computing the weekday, previous Monday, Monday date, future Sunday date, and making a request based on the calculated dates. However, a more concise approach utilizing the YEARWEEK() function is available.

YEARWEEK() is a MySQL function that returns the week number of a given date, with the default starting day set to Sunday. To query for records within the current week, including Monday through Sunday, use YEARWEEK() as follows:

<code class="sql">SELECT *
FROM   your_table
WHERE  YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)</code>

This query determines the week number for each row's date column, considering Sunday as the first day of the week (parameter 1). It then compares the week number of each row to the week number of the current date (CURDATE()), effectively retrieving records that fall within the same week.

Correcting the Date Range for Week Records

The example query provided in the question:

<code class="sql">SELECT *
FROM   temp
WHERE  yearweek(`date`) = yearweek(curdate())</code>

incorrectly retrieves records from Sunday to Saturday (17.11.2013 to 23.11.2013) due to the default starting day of Sunday. To adjust for Monday as the first day of the week and retrieve records from 18.11.2013 to 24.11.2013, use the following modified query:

<code class="sql">SELECT *
FROM   temp
WHERE  YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)</code>

By specifying the starting day as Monday (parameter 1), the YEARWEEK() function ensures that the week boundaries align with the desired range.

The above is the detailed content of How to Query MySQL for Records Within the Current Week?. For more information, please follow other related articles on the PHP Chinese website!