How to Query MySQL for Data from the Last Week Starting on Sunday?

Retrieving MySQL Data from the Last Week (Sunday Start)

Database queries often require filtering data based on specific timeframes. This guide demonstrates how to efficiently select MySQL entries from the past week, starting on Sunday.

Scenario:

Imagine a table with a 'date' column and other relevant data. The goal is to extract all records from the preceding week, considering Sunday as the week's beginning. Example table data:

id	date
2	2011-05-14 09:17:25
5	2011-05-16 09:17:25
6	2011-05-17 09:17:25
8	2011-05-20 09:17:25
15	2011-05-22 09:17:25

In this case, IDs 5, 6, and 8 should be selected because their dates fall within the last week.

MySQL Query:

The following query effectively accomplishes this:

<code class="language-sql">SELECT id FROM tbname WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW();</code>

This query uses DATE_SUB() to calculate the date one week ago from the current time (NOW()). The BETWEEN operator then filters records where the 'date' column falls within this week-long interval.

Outcome:

Running this query on the sample table produces the following result:

id

5

6

8

This approach provides a concise and accurate method for retrieving data from the past week, starting on Sunday, in MySQL.

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