How to query the data of the past week in mysql-Mysql Tutorial-php.cn

How to query the data of the past week in mysql

WBOY

Release： 2022-03-31 18:28:30

Original

9859 people have browsed it

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)

How to query the data of the past week in mysql

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);

Copy after login

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();

Copy after login

Query for one month:

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

Copy after login

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):

How to query the data of the past week in mysql

Calendar:

How to query the data of the past week in mysql

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(),&#39;%w&#39;)-1)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 1 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 2 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 3 day)) as thisweek  
        union all  
        SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 4 day)) as thisweek  
        union all  
        SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 5 day)) as thisweek  
        union all  
        SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),&#39;%w&#39;)-1), interval 6 day)) as thisweek

Copy after login

How to query the data of the past week in mysql

Analysis:

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

Copy after login

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!