How to query data of mysql 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:

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());

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:

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

Query yesterday's information records:

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

Query Information records in the last seven 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 Information records for this month:

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

Query the information records of the last month:

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:

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 |+--------------------------------+

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:

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, for example:

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                                    |+--------------------------------------------------------+

The procedure is as follows:

select str_to_date(article.`add_time`,'%Y-%m-%d %H:%i:%s')from articlewhere 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';

Articles you may be interested in

• Note the following when querying strings with single quotes and inserting strings with single quotes in Mysql:
• Php output yesterday, today, tomorrow is the day of the week
• How does mysql export data of a field in a table?
• Summary of the causes and solutions for slow mysql Server Query
• Php obtains the start and end timestamps of today, yesterday, last week, and this month.
• MySQL query optimization skills
• How to optimize servers, static databases, and load balancing for high-traffic websites
• Php summarizes the N methods used to obtain the timestamp of today, tomorrow, and yesterday