Methods for Querying Last Week Data Starting from Sunday in MySQL

Problem Background and Requirement Analysis

In database applications, there is often a need to statistics and analyze data on a weekly basis. According to the user-provided Q&A data, the core requirement is to query all records from the previous week in a table containing date fields, where the week is defined as starting from Sunday. Example data illustrates the specific query scenario:

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

The expected query result should only include records with ids 5, 6, and 8, because id=2's date (2011-05-14) belongs to the week before last, while id=15's date (2011-05-22) belongs to the current week.

Core Solution: DAYOFWEEK Function Approach

Based on the best answer (score 10.0) from the Q&A data, we employ MySQL's DAYOFWEEK function to achieve precise week boundary calculations. The DAYOFWEEK function returns the weekday index for a date, where 1 represents Sunday, 2 represents Monday, and so on up to 7 representing Saturday.

The complete query statement is as follows:

SELECT id FROM tbl
WHERE date >= CURDATE() - INTERVAL (DAYOFWEEK(CURDATE()) + 6) DAY
AND date < CURDATE() - INTERVAL (DAYOFWEEK(CURDATE()) - 1) DAY

Let's analyze the logic of this query step by step:

First, DAYOFWEEK(CURDATE()) returns the numeric representation of the current day of the week. Assuming the current date is Monday (DAYOFWEEK=2), then:

• End date of last week calculation: CURDATE() - INTERVAL (2 - 1) DAY = Last Sunday
• Start date of last week calculation: CURDATE() - INTERVAL (2 + 6) DAY = Sunday of the week before last

This calculation method ensures the query range is strictly limited to the complete previous week, from Sunday to Saturday, fully complying with the user's requirement definition.

Alternative Solutions Comparative Analysis

Simple Date Range Query

The first solution in the Q&A data uses a simple date range:

SELECT id FROM tbname
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW()

Although this method is concise, it has significant drawbacks: it queries data from the past 7 days, not the complete previous week. If today is Wednesday, it would return data from last Wednesday to this Wednesday, which does not match the definition of "last week starting from Sunday."

YEARWEEK Function Method

The third solution uses the YEARWEEK function:

SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)

The YEARWEEK function returns a combination of year and week number, with the week starting from Sunday by default. This method is conceptually closer to the requirement but may have inconsistencies in certain edge cases, such as week number calculations across year boundaries.

Practical Application Extensions

The reference article provides broader time range query scenarios, including statistics queries for current week, last week, and last month. We can apply these concepts to our solution:

Query data for current week (starting from Sunday):

SELECT id FROM tbl
WHERE date >= CURDATE() - INTERVAL (DAYOFWEEK(CURDATE()) - 1) DAY
AND date < CURDATE() - INTERVAL (DAYOFWEEK(CURDATE()) - 8) DAY

Query data for last month:

SELECT id FROM tbl
WHERE YEAR(date) = YEAR(CURDATE() - INTERVAL 1 MONTH)
AND MONTH(date) = MONTH(CURDATE() - INTERVAL 1 MONTH)

Performance Optimization Considerations

In production environments, index usage on date fields is crucial for query performance. It is recommended to create an index on the date field, especially when dealing with large tables. The advantage of the DAYOFWEEK method is that it can fully utilize date indexes, while the YEARWEEK method may require function-based indexes on calculation results to achieve optimal performance.

For high-frequency query scenarios, consider using materialized views or pre-computed tables to store weekly aggregated data, thereby avoiding complex date calculations in each query.

Edge Case Handling

When dealing with week boundaries, special attention should be paid to timezone issues. If the application involves users across multiple timezones, it is recommended to store dates in UTC time and perform timezone conversions during queries. Additionally, for data insertion operations that cross week boundaries, ensure consistency in business logic.

Another important edge case is daylight saving time adjustments. During DST transitions, date calculations may exhibit anomalies, so thorough testing is recommended in critical business scenarios.

Conclusion

Through in-depth analysis of MySQL's date functions and query optimization techniques, we have provided multiple solutions for querying last week's data. The DAYOFWEEK function approach stands out as the preferred method due to its precision and performance advantages, while YEARWEEK and simple range queries serve as alternatives with their respective applicable scenarios. Developers should choose the most appropriate implementation based on specific business requirements, data scale, and performance needs.