SQL for date query related operations in MySQL database

Sometimes we have to count the data of the date specified in the database, such as the usual, today, yesterday, tomorrow, this week, last week, this month, the last month's data, as well as the date of the addition and subtraction, except we can use Strtotime to calculate also can refer to the following method. SQL minus the specified number of days

SELECT * FROM table name where Date_format (Time field, "%y-%m-%d") = Date_add (input time, INTERVAL-1 day);

/** If you need to add a specified number of days-1 to the specified number of days */

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

/*datediff (now (), From_unixtime (' Time field name ')) = 7; Time field Storage Type is timestamp */

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

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 (), INTER VAL 1 MONTH), '%y-%m ')

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

Because it didn't work so often, leave a note ~ ~ ~

SQL for date query related operations in MySQL database