MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the data of the previous monthly method analysis Summary:
In other words, there is an article table article, the time to store the article is the Add_time field, which is type int (5), now needs to query the total number of articles added today and sorted by time from big to small, the query statement is as follows:
Copy Code code as follows:
SELECT * from ' article ' where Date_format (From_unixtime (' add_time '), '%y-%m-%d ') = Date_format (now (), '%y-%m-%d ');
Or:
Copy Code code as follows:
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 for the above table is a datetime type or a timestamp type, the query statement can also be written as follows:
Check today's information records:
Copy Code code as follows:
SELECT * from ' article ' where to_days (' add_time ') = To_days (now ());
Query yesterday's information records:
Copy Code code as follows:
SELECT * from ' article ' where To_days (now ()) –to_days (' Add_time ') <= 1;
Inquire about the information records of the last 7 days:
Copy Code code as follows:
SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL 7 day) <= date (' Add_time ');
Inquire about the information records of the last 30 days:
Copy Code code as follows:
SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL Day) <= date (' Add_time ');
Check this month's information record:
Copy Code code as follows:
SELECT * from ' article ' where Date_format (' add_time ', '%y%m ') = Date_format (Curdate (), '%y%m ');
Check the information records of the previous January:
Copy Code code as follows:
SELECT * from ' article ' where Period_diff (the Date_format (now (), '%y%m '), Date_format (' add_time ', '%y%m ') = 1;
Do an analysis of several functions in the above SQL statement:
(1) To_days
Like its name, it is the Unix timestamp that converts a specific date or time string to one day, such as:
Copy Code code as follows:
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 |
+--------------------------------+
You can see the difference between 22nd and 23rd is that after the conversion of the number increased by 1, the granularity of the query is relatively rough, 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 of the Gregorian calendar (1582), because the date of the change is not taken into account. 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 bits. So for ' 1997-10-07 ' and ' 97-10-07 ' will be considered the same date:
Copy Code code as follows:
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:
Copy Code code as follows:
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 actions are as follows:
Copy Code code 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 ';
