How to Retrieve Data from the Past Week Using a SQL Query?

Extracting Data from the Last Seven Days with SQL

This guide demonstrates how to retrieve data from the past week using a SQL query, defining the week as starting on Sunday. Let's say you have a table with a 'date' column and other relevant data. The objective is to select entries from the last seven days.

Example Table:

Consider a table with the following sample 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

The goal is to extract id values 5, 6, and 8, representing data from the preceding week.

SQL Query:

The following MySQL query achieves this:

SELECT id
FROM tbname
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();

Explanation:

This query employs the BETWEEN operator to filter records where the 'date' falls within a specific timeframe. DATE_SUB(NOW(), INTERVAL 7 DAY) calculates the date and time seven days prior to the current moment (NOW()). The BETWEEN clause then selects entries between this calculated date and the current date and time.

Result:

The query returns the following:

id

5

6

8

These id values accurately reflect the data entries from the past week. Note that this query considers the last seven days regardless of the day of the week. For a more complex week definition (e.g., starting on Sunday), more sophisticated date manipulation might be necessary, potentially involving the DAYOFWEEK() function or similar.

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