Mysql databases are queried by day, yesterday, the first seven days, the last thirty days, the quarter, and the year. mysql databases are queried by thirty days.

Mysql databases are queried by day, yesterday, the first seven days, the last thirty days, the quarter, and the year. mysql databases are queried by thirty days.

Today, I spent some time sorting out the code for MySQL to find data for the current day, yesterday, last week, last month, and so on.

1. query statements of today's data

Select * frim table name where to_days (time field name) = to_days (now ());
Select now (); // obtain the current time format: 23:21:33
Select curdate (); // obtain the current time format:
Select curtime (); // obtain the current time format: 23: 19: 43

2. Statement for querying yesterday's data

Select * from table name where to_days (now ()-to_days (time field name) = 1;

3. query seven-day statements

Select * from table name where date_sub (curdate (), interval 7 day) <= date (time field name ); or select * from table name where time field Name> = date_sub (curdate (), interval 7 day );

4. query statements for the last month

Select * from table name where date_sub (curdate (), interval 1 month) <= date (time field name ); or select * from table name where time field> = date_sub (curdate (), interval 1 month );

5. query data for the current quarter

Select * from table name where QUARTER (time field name) = QUARTER (now ());
QUARTER (date); // The date of the year returned. The value range is 1 to 4 quarters.

6. query the data of the last quarter

Select * from table name where QUARTER (time field name) = QUARTER (DATE_SUB (now (), interval 1 QUARTER ));

6. query data of the current year

Select * from table name where YEAR (time field name) = YEAR (NOW ());

7. query last year's data

Select * from table name where year (time field name) = year (date_sub (now (), interval 1 year ));

8. query data for the current week

SELECT name, time field name FROM table name where yearweek (date_format (submittime, '% Y-% m-% D') = YEARWEEK (now ());

9. query last week's data

SELECT name, time field name FROM table name where yearweek (date_format (submittime, '% Y-% m-% D') = YEARWEEK (now ()-1;

10. query the data of the current month

Select name, time field name from table name where date_format (submittime, '% Y-% m') = date_format (now (),' % Y-% m ')

11. query the data of the previous month

Select name, time field name from table name where date_format (time field name, '% Y-% m') = date_format (DATE_SUB (curdate (), INTERVAL 1 MONTH ), '% Y-% m ');
DATE_FORMAT (); // function is used to display date/time data in different formats.

12. MySQL Date and Time Functions

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
Mysql> select DAYOFWEEK ('2017-02-03 ');-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday)
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');-> 5
Mysql> select WEEKDAY ('2017-11-05 ');-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');-> 2