MySQL collects logs of the previous day. logs of this week are collected on a specific day.

In the mysql database, count the logs of the current day:

For example, the Date Field of the bean database is usedate.

Calculate the log SQL statement for the current day:

select * from bean where date(usedate) = curdate();

Curdate () indicates the date of the current day.

If it indicates the data of the previous day, you cannot use curdate ()-1, because when the date is the beginning of the month, curdate ()-1 is not the last date of the previous month

For example, Today is July 22, July 1. In theory, curdate ()-1 is July 22, June 30, but curdate ()-1 is not July 22, June 30.

Therefore, you cannot use curdate ()-1 to calculate the date of the previous day, and the mysql database has a new method to calculate the data of the previous day.

Calculate the log SQL statement of the previous day:

select * from bean where date(usedate) = date_sub(curdate(),interval 1 day);

It is the day before the current day in parentheses. If the number of days is counted, change '1' in brackets to the corresponding number of days.

Weekly log statistics

Requirement: collect statistics from yesterday. the first seven days of logs include yesterday.

For example, we will collect data from July 1.

select * from bean where date(usedate) >= date_sub(curdate(),interval 7 day) 

and date(usedate) <=  date_sub(curdate(),interval 1 day)

Week is used on the Internet to collect weekly information, which can only be used for 5 days. This method is used because it does not meet the requirements.

Collect logs of a previous day

Replace curdate () in the date_sub (curdate (), interval 1 day) function with the date of a certain day.

For example, you need to collect the information of the date.

date_sub('2010-07-05',interval 1 day)