How to Select Data from the Past Week in MySQL?

Tips for querying past week data in MySQL

In database queries, it is often necessary to filter data within a specific time range. In MySQL, you can use the BETWEEN operator to extract records within a specified time period.

Question: Suppose you have a table with a date field and you need to select all entries from the past week. The week starts on Sunday.

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

Expected output:

id

5

6

8

SQL statement:

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

Instructions:

The
• DATE_SUB function subtracts one week from the current date (NOW()). This gives us the start date for the previous week.
The
• BETWEEN operator checks whether a date field is greater than or equal to the previous week's start date, and less than or equal to the current date.
• This query selects all rows that meet the date criteria.

This query can effectively obtain data for the past week. Note that the NOW() function returns the current time of the server. If calculations need to be based on a specific time zone or other point in time, adjustments need to be made accordingly.

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