mysql queries SQL statements for today yesterday last 7 days last 30 days this month last Month

Method analysis and summary of mysql query data of today, yesterday, recent 7 days, recent 30 days, this month, last January:
There is 1 article table article, and the time of adding articles is add_time field, which is type int(5). Now we need to query the total number of articles added today and sort them by time from large to small, then the query statement is as follows:

select * from `article` where date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d');

Or:

select * from `article` where to_days(date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d')) = to_days(now());

Assuming that the add_time field of the above table has a storage type of DATETIME or TIMESTAMP, the query statement can also be written as follows:
Query today's information record:

select * from `article` where to_days(`add_time`) = to_days(now());

Query yesterday's information record:

select * from `article` where to_days(now())  �  to_days(`add_time`) <= 1;

Query the information record of the last 7 days:

select * from `article` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`add_time`);

Query the information records of the last 30 days:

select * from `article` where date_sub(curdate(), INTERVAL 30 DAY) <= date(`add_time`);

Query the information record of this month:

select * from `article` where date_format(`add_time`,  ' %Y%m') = date_format(curdate() ,  ' %Y%m');

Query the information record of last January:

select * from `article` where period_diff(date_format(now() ,  ' %Y%m') , date_format(`add_time`,  ' %Y%m')) =1;

Make 1 analysis of several functions in the SQL statement above:
(1) to_days
Like its name 1, it converts a specific date or time string to an unix timestamp corresponding to a specific day, such as:

mysql> select  to_days('2010-11-22 14:39:51');      

 +--------------------------------+                                                        

| to_days('2010-11-22 14:39:51') |

+--------------------------------+

|                         734463 |

+--------------------------------+
mysql> select  to_days('2010-11-23 14:39:51');

+--------------------------------+

| to_days('2010-11-23 14:39:51') |

+--------------------------------+

|                         734464 |

+--------------------------------+

The difference between the 22nd and 23rd is that the number after the transformation is increased by 1. This granular query is relatively crude and may not meet our query requirements, so we need to use the fine-grained query method str_to_date function, which will be analyzed below.

Remind:
(1) to_days() is not used for values before the solar calendar appears (1582) because when the calendar changes, the lost date will not be taken into account. Therefore, for dates prior to 1582 (and perhaps the following year in other regions), the results of this function are unreliable.

(2) The rule in MySQL" Date and Time type "is to convert the two-digit year value in the date into four digits. Therefore '1997-10-07' and '97-10-07' will be treated as the same date:

mysql> select to_days('1997-10-07'), to_days('97-10-07'); 

    -> 729669, 729669

(2) str_to_date
This function can fully translate the string time, such as:

select * from `article` where to_days(date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d')) = to_days(now());

0

Specific case operations are 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')>='2012-06-28 08:00:00' and str_to_date(article.`add_time`,'%Y-%m-%d %H:%i:%s')<='2012-06-28 09:59:59';