Mysql Query data method today, yesterday, last 7 days, last 30 days, last month, and last month

Mysql Query today, yesterday, last 7 days, last 30 days, this month, the last month data method Analysis Summary: No source code and demonstration: demonstrate the source, there is an article table article, the time for adding an article is the add_time field. This field is of the int (5) type. you need to query the total number of articles added today and sort them by time in ascending order, the query statement is as follows:

Mysql Query today, yesterday, last 7 days, last 30 days, this month, the last month data method Analysis Summary: No source code and demonstration: demonstrate the source, there is an article table article, the time for adding an article is the add_time field. This field is of the int (5) type. you need to query the total number of articles added today and sort them by time in ascending order, the query statement is as follows:

Mysql Query today, yesterday, last 7 days, last 30 days, and last month, data method analysis summary:
<无>

Source code and demo:Demonstration source

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: 1 select * from 'article' where date_format (from_UNIXTIME ('add _ time'), '% Y-% m-% d') = date_format (now (), '% Y-% m-% d'); or: 1 select * from 'article' where to_days (date_format (from_UNIXTIME ('add _ time '), '% Y-% m-% d') = to_days (now (); assume that the add_time field of the preceding table is of the DATETIME or TIMESTAMP type, the query statement can also be written as follows: query the information record of today: 1 select * from 'ar Ticle 'Where to_days ('add _ time') = to_days (now (); query the information record of Yesterday: 1 select * from 'article' where to_days (now ()) -to_days ('add _ time') <= 1; query information records for the last seven days: 1 select * from 'article' where date_sub (curdate (), INTERVAL 7 DAY) <= date ('add _ time'); query information records for the last 30 days: 1 select * from 'article' where date_sub (curdate (), INTERVAL 30 DAY) <= date ('add _ time'); query information records for this month: 1 select * from 'article' where date_format ('add _ time ', '% Y % M') = date_format (curdate (),' % Y % M'); query information records of the previous month: 1 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 is like its name, it converts a specific date or time string to the unix timestamp corresponding to a day, for example, 01 mysql> select to_days ('2017-11-22 14:39:51 '); 02 + ------------------------------ + 03 | to_days ('2017-11-22 14:39:51 ') | 04 + -------------------------------- + 05 | 734463 | 06 + ------------------------------ + 07 08 mysql> select to_days ('2017-11-23 14:39:51 '); 09 + -------------------------------- + 10 | to_days ('2017-11-23 14:39:51 ') | 11 + ------------------------------ + 12 | 734464 | 13 + ---------------------------------- + we can see that the difference between the 22nd and 23rd is that the number after conversion increases by 1, the query at this granularity is rough and sometimes cannot meet our query requirements. 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 the value before the occurrence of the Gregorian calendar (1582), 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, for '2017-10-07' and '97-10-07', the same date will be considered: 1 mysql> select to_days ('2017-10-07 '), to_days ('97-10-07 '); 2 3-> 729669,729 669 (2) the str_to_date function can fully translate the string time, for example: 1 mysql> select str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % S '); 2 3 + ---------------------------------------------------- + 4 | str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % S ') | 5 + records + 6 | 14:39:51 | 7 + ---------------------------------------------------------- + specific case operations: 1 select str_to_date (article. 'Add _ time', '% Y-% m-% d % H: % I: % S') 2 from article3 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 ';