Mysql query today, yesterday, last 7 days, last 30 days, this month, last month data method

There is an article table article. The time of adding articles is stored in the add_time field. This field is of type int(5). Now we need to query the total number of articles added today and sort them by time from large to small. 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 storage type of the add_time field in the above table is DATETIME type or TIMESTAMP type, 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 recent information records 7 days of information records:

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

Query the information records of the past 30 days:

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

Query the information records of this month:

    SELECT * FROM `article` where date_format(`add_time`, ‘%Y%m') = date_format(curdate() , ‘%Y%m');

Query the information records of the previous month:

SELECT * FROM `article` where period_diff(date_format(now() , ‘%Y%m') , date_format(`add_time`, ‘%Y%m')) =1;

is interested in several of the above SQL statements Let’s do some analysis of the function:

(1) to_days

Just like its name, it converts a specific date or time string to the unix timestamp corresponding to a certain 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 |
   +--------------------------------+

can see 22 The difference between the day and the 23rd is that the number after conversion increases by 1. This granular query is relatively rough and sometimes may not meet our query requirements. Then we need to use the fine-grained query method str_to_date function, which will be analyzed below. Usage of this function.

Reminder:

(1) to_days() is not used for values ​​before the advent of the solar calendar (1582). The reason is that when the calendar changes, the missing dates will not be taken into account. Therefore, the results of this function are unreliable for dates before 1582 (or perhaps the next year in other regions).

(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 regarded 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 completely translate the string time, such as:

 
    mysql> select str_to_date("2010-11-23 14:39:51",'%Y-%m-%d %H:%i:%s');
    
    +--------------------------------------------------------+
    | str_to_date("2010-11-23 14:39:51",'%Y-%m-%d %H:%i:%s') |
    +--------------------------------------------------------+
   | 2010-11-23 14:39:51                                    |
  +--------------------------------------------------------+

specific case Here’s how:

select str_to_date(article.`add_time`,'%Y-%m-%d %H:%i:%s')
   from article
    where str_to_date(article.`add_time`,&#39;%Y-%m-%d %H:%i:%s&#39;)>=&#39;2012-06-28 08:00:00&#39; and str_to_date(article.`add_time`,&#39;%Y-%m-%d %H:%i:%s&#39;)<=&#39;2012-06-28 09:59:59&#39;;