MYSQL queries data from yesterday, this week, and so on.

MYSQL queries data from yesterday, this week, and so on.

Mysql Query this quarter

Today

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

Yesterday

SELECT * FROM table name WHERE TO_DAYS (NOW ()-TO_DAYS (time field name) <= 1

7 days

SELECT * FROM table name where DATE_SUB (CURDATE (), INTERVAL 7 DAY) <= date (time field name)

Last 30 days

SELECT * FROM table name where DATE_SUB (CURDATE (), INTERVAL 30 DAY) <= date (time field name)

This month

SELECT * FROM table name WHERE DATE_FORMAT (time field name, '% Y % M') = DATE_FORMAT (CURDATE (),' % Y % M ')

Last month

SELECT * FROM table name WHERE PERIOD_DIFF (date_format (now (), '% Y % M'), date_format (time field name,' % Y % M') = 1

# Querying data for the current quarter
Select * from 'ht _ invoice_information' where QUARTER (create_date) = QUARTER (now ());
# Querying data of the last quarter
Select * from 'ht _ invoice_information' where QUARTER (create_date) = QUARTER (DATE_SUB (now (), interval 1 QUARTER ));
# Querying current year data
Select * from 'ht _ invoice_information 'where YEAR (create_date) = YEAR (NOW ());
# Querying data of the previous year
Select * from 'ht _ invoice_information 'where year (create_date) = year (date_sub (now (), interval 1 year ));

Query data for the current week
SELECT name, submittime FROM enterprise where yearweek (date_format (submittime, '% Y-% m-% d') = YEARWEEK (now ());

Query last week's data
SELECT name, submittime FROM enterprise where yearweek (date_format (submittime, '% Y-% m-% d') = YEARWEEK (now ()-1;

Query data of the current month
Select name, submittime from enterprisewhere date_format (submittime, '% Y-% M') = date_format (now (),' % Y-% M ')

Query data 6 months from the current time
Select name, submittime from enterprise where submittime between date_sub (now (), interval 6 month) and now ();

Query data of last month
Select name, submittime from enterprisewhere date_format (submittime, '% Y-% M') = date_format (DATE_SUB (curdate (), INTERVAL 1 MONTH),' % Y-% M ')

Select * from 'user' whereDATE _ FORMAT (pudate, '% Y % M') = DATE_FORMAT (CURDATE (),' % Y % M ');

Select * from user where WEEKOFYEAR (FROM_UNIXTIME (pudate, '% y-% m-% d') = WEEKOFYEAR (now ())

Select *
Fromuser
WhereMONTH (FROM_UNIXTIME (pudate, '% y-% m-% d') = MONTH (now ())

Select *
From [user]
WhereYEAR (FROM_UNIXTIME (pudate, '% y-% m-% d') = YEAR (now ())
AndMONTH (FROM_UNIXTIME (pudate, '% y-% m-% d') = MONTH (now ())

Select *
From [user]
Wherepudatebetween last day of last month
And the first day of next month

Wheredate (regdate) = curdate ();

Select * fromtestwhereyear (regdate) = year (now () andmonth (regdate) = month (now () andday (regdate) = day (now ())

SELECT date (c_instime), curdate ()
FROM't _ score'
WHERE 1
LIMIT 0, 30