How to Retrieve MySQL Data from the Past Week Starting on Sunday?

Extracting MySQL Data for the Last Week (Sunday Start)

This guide demonstrates how to retrieve MySQL data from the past week, beginning on the preceding Sunday. We'll leverage MySQL's date functions to achieve this.

The MySQL Query

The following query efficiently filters data to include only records from the last seven days, starting from the previous Sunday:

<code class="language-sql">SELECT id
FROM tbname
WHERE date BETWEEN date_sub(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY) AND CURDATE();</code>

Detailed Explanation

• CURDATE(): Returns the current date.
• DAYOFWEEK(CURDATE()): Determines the day of the week (1=Sunday, 2=Monday,...,7=Saturday) for the current date.
• INTERVAL DAYOFWEEK(CURDATE())-1 DAY: Calculates the number of days to subtract to reach the previous Sunday. If today is Sunday, it subtracts 0 days; if today is Monday, it subtracts 1 day, and so on.
• date_sub(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY): Subtracts the calculated number of days from the current date, resulting in the date of the previous Sunday.
• BETWEEN ... AND CURDATE(): Selects records where the 'date' column falls within the range from the previous Sunday to the current date.

Illustrative Example

Consider this sample table:

Running the query above will return:

This accurately retrieves IDs (5, 6, and 8) corresponding to entries within the past week, starting from the preceding Sunday. Note that the specific results will depend on the current date when the query is executed.

The above is the detailed content of How to Retrieve MySQL Data from the Past Week Starting on Sunday?. For more information, please follow other related articles on the PHP Chinese website!