MySQL Common date operation

MySQL query today, yesterday, 7 days, nearly 30 days, this month, last month data 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)

Nearly 30 days

SELECT * FROM table name where Date_sub (Curdate (), INTERVAL Day) <= Date (Time field name)

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

#查询本季度数据

SELECT * from ' ht_invoice_information ' where QUARTER (create_date) =quarter (now ());

#查询上季度数据

SELECT * from ' ht_invoice_information ' where QUARTER (create_date) =quarter (Date_sub (now (), Interval 1 QUARTER));

#查询本年数据

SELECT * from ' Ht_invoice_information ' where year (create_date) =year (now ());

#查询上年数据

SELECT * from ' Ht_invoice_information ' where year (create_date) =year (Date_sub (now (), interval 1));

Querying data for the current week

SELECT Name,submittime from the Enterprise WHERE Yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ());

Query last week's data

SELECT Name,submittime from the Enterprise WHERE Yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ())-1;

Querying data for the current month

Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (now (), '%y-%m ')

Query data that is currently 6 months from now

Select Name,submittime from Enterprise where Submittime between Date_sub (now (), Interval 6 month) and now ();

Querying data for the previous month

Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (Date_sub (Curdate (), INTERVAL 1 MONTH), '%y-%m ')

SELECT * from ' user ' where Date_format (pudate, '%y%m ') = Date_format (Curdate (), '%y%m ');

SELECT * from user where WeekOfYear (from_unixtime (pudate, '%y-%m-%d ')) = WeekOfYear (now ())

SELECT *

From user

Where month (From_unixtime (pudate, '%y-%m-%d ')) = month (now ())

SELECT *

from [user]

Where year (From_unixtime (pudate, '%y-%m-%d ')) = year (now ())

And month (From_unixtime (pudate, '%y-%m-%d ')) = month (now ())

SELECT *

from [user]

Where pudate between the last day of last month

And the first day of next month

where date (regdate) = Curdate ();

SELECT * FROM Test where year (regdate) =year (now ()) and month (regdate) =month (now ()) and Day (regdate) =day (now ())

SELECT Date (c_instime), Curdate ()

From ' T_score '

WHERE 1

LIMIT 0, 30

MySQL Common date operation