How to query the data of the past week in mysql

In mysql, you can use the select statement to query data for the past week. The syntax is "select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY)

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query the data of the past week with mysql

The syntax is as follows:

select * from table  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

Expand knowledge:

Query for one day:

select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate();

Query for one month:

select * from table  where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);

The example is as follows:

The effect is as shown in the figure (the week range in the SQL statement here refers to the day of the week) Monday to Sunday, Monday is the first day of the week. Since the query was on August 11, only the results from Monday to Saturday are displayed):

Calendar:

To put it simply, use today’s date to generate the dates of the previous seven days (using the union all command), and select this week’s date based on Monday’s date conditions

        SELECT  DATE(subdate(curdate(),date_format(curdate(),'%w')-1)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 1 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 2 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 3 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 4 day)) as thisweek  
        union all  
        SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 5 day)) as thisweek  
        union all  
        SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 6 day)) as thisweek

Analysis:

SELECT DATE(subdate(curdate(),date_format(curdate(),’%w’)-1))

gets the first day of the week (Monday to Sunday is a week); that is, August 6

Recommended learning: mysql video tutorial

The above is the detailed content of How to query the data of the past week in mysql. For more information, please follow other related articles on the PHP Chinese website!