Board Deng: MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the last month data method

MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the last month of data method Analysis Summary:

There is an article table article, the time to store the article's add-on is the Add_time field, which is of type int (5), and now you need to query the total number of articles added today and sort 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 storage type of the Add_time field for the table above is a datetime type or timestamp type, 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 record:

SELECT * from ' article ' where To_days (now ()) –to_days (' Add_time ') <= 1;

Check the information record for nearly 7 days:

SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL 7 day) <= date (' Add_time ');

Check the information record for nearly 30 days:

SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL-day) <= date (' Add_time ');

Query this month's information records:

SELECT * from ' article ' where Date_format (' add_time ', '%y%m ') = Date_format (Curdate (), '%y%m ');

Query the information record for the previous January:

SELECT * from ' article ' where Period_diff (Date_format (now (), '%y%m '), Date_format (' add_time ', '%y%m ')) = 1;
Take a look at some of the functions in the SQL statement above:

(1) To_days

Like its name, it is a Unix timestamp that converts a specific date or time string to a 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 |
+ —————————— –+
It can be seen that the difference between 22nd and 23rd is that the number of conversions increased by 1, the granularity of the query is relatively coarse, and sometimes may not meet our query requirements, then we need to use a fine-grained query method Str_to_date function, the following will analyze the use of this function.

Remind:

(1) to_days () is not used for the value before the Gregorian calendar (1582), because the lost date is not taken into account when the calendars are changed. Therefore, the results of this function are not reliable for dates prior to 1582 (perhaps in other regions for the next year).

(2) The rule in MySQL date and time type is to convert the two-digit year value in the date to four-bit. So for ' 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 translate the string time completely, 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 |
+ —————————————————— –+
The specific case actions 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′;

Board Deng: MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the previous month data method