Mysql queries SQL statements of today, yesterday, last 7 days, last 30 days, last month, and last month

Mysql query method analysis summary:
In other words, the article table article stores the time when an article is added as the add_time field, which is of the int (5) type, to query the total number of articles added today in descending order of time, the query statement is as follows:
Copy codeThe Code is as follows:
Select * from 'Article' where date_format (from_UNIXTIME ('add _ Time'), '% Y-% m-% D') = date_format (now (), '% Y-% m-% D ');

Or:
Copy codeThe Code is as follows:
Select * from 'Article' where to_days (date_format (from_UNIXTIME ('add _ Time'), '% Y-% m-% D') = to_days (now ());

If the storage type of the add_time field in the preceding table is DATETIME or TIMESTAMP, the query statement can also be written as follows:
Query today's information records:
Copy codeThe Code is as follows:
Select * from 'Article' where to_days ('add _ Time') = to_days (now ());

Query yesterday's information records:
Copy codeThe Code is as follows:
Select * from 'Article' where to_days (now ()-to_days ('add _ Time') <= 1;

Query Information records in the last seven days:
Copy codeThe Code is as follows:
Select * from 'Article' where date_sub (curdate (), INTERVAL 7 DAY) <= date ('add _ Time ');

Query the information records of the last 30 days:
Copy codeThe Code is as follows:
Select * from 'Article' where date_sub (curdate (), INTERVAL 30 DAY) <= date ('add _ Time ');

Query Information records for this month:
Copy codeThe Code is as follows:
Select * from 'Article' where date_format ('add _ time', '% Y % m') = date_format (curdate (),' % Y % m ');

Query the information records of the last month:
Copy codeThe Code is as follows:
Select * from 'Article' where period_diff (date_format (now (), '% Y % m'), date_format ('add _ time',' % Y % m ')) = 1;

Analyze several functions in the preceding SQL statement:
(1) to_days
Like its name, it converts a specific date or time string to the unix timestamp corresponding to a day, for example:

Copy codeThe Code is as follows:
Mysql> select to_days ('2017-11-22 14:39:51 ');
+ -------------------------------- +
| To_days ('2017-11-22 14:39:51 ') |
+ -------------------------------- +
| 1, 734463 |
+ -------------------------------- +

Mysql> select to_days ('2017-11-23 14:39:51 ');
+ -------------------------------- +
| To_days ('2017-11-23 14:39:51 ') |
+ -------------------------------- +
| 1, 734464 |
+ -------------------------------- +

We can see that the difference between the 22nd and 23rd is that the number after conversion increases by 1, and the query granularity is rough and sometimes cannot meet our query requirements, then we need to use the fine-grained query method str_to_date function. The usage of this function will be analyzed below.

Reminder:
(1) to_days () is not used for values before the occurrence (1582) of the Gregorian calendar, because when the calendar changes, the lost date is not taken into account. Therefore, for the date before January 1, 1582 (maybe another region is the next year), the result of this function is unreliable.
 
(2) The rule in MySQL "Date and Time type" is to convert the two-digit year value in the date to four digits. Therefore, '2017-10-07' and '97-10-07' are treated as the same date:
Copy codeThe Code is as follows:
Mysql> select to_days ('2017-10-07 '), to_days ('97-10-07 ');
-> 729669,729 669

(2) str_to_date
This function can completely translate the string time, for example:
Copy codeThe Code is as follows:
Mysql> select str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % s ');
 
+ -------------------------------------------------------- +
| Str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % s') |
+ -------------------------------------------------------- +
| 14:39:51 |
+ -------------------------------------------------------- +

The procedure is as follows:
Copy codeThe Code is as follows:
Select str_to_date (article. 'add _ time', '% Y-% m-% d % H: % I: % s ')
From article
Where str_to_date (article. 'Add _ time', '% Y-% m-% d % H: % I: % s')> = '2017-06-28 08:00:00' and str_to_date (article. 'Add _ time', '% Y-% m-% d % H: % I: % s') <= '2017-06-28 09:59:59 ';