MySQL Query SQL statements today, yesterday, nearly 7 days, nearly 30 days, this month, and last January

MySQL Query SQL statements today, yesterday, nearly 7 days, nearly 30 days, this month, and last January

This article mainly introduces the MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the January of SQL statements, generally in some statistical reports more commonly used this time period, the need for friends can refer to the next

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:

The code is as follows:
SELECT * from ' article ' where Date_format (From_unixtime (' add_time '), '%y-%m-%d ') = Date_format (now (), '%y-%m-%d ');

Or:

The code is as follows:
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:

The code is as follows:
SELECT * from ' article ' where to_days (' add_time ') = To_days (now ());

Query yesterday's information record:

The code is as follows:
SELECT * from ' article ' where To_days (now ()) –to_days (' Add_time ') <= 1;

Check the information record for nearly 7 days:

The code is as follows:
SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL 7 day) <= date (' Add_time ');

Check the information record for nearly 30 days:

The code is as follows:
SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL-day) <= date (' Add_time ');

Query this month's information records:

The code is as follows:
SELECT * from ' article ' where Date_format (' add_time ', '%y%m ') = Date_format (Curdate (), '%y%m ');

Query the information record for the previous January:

The code is as follows:
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:

The code is 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 |
+--------------------------------+

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 considered the same date:

The code is as follows:
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:

The code is 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 |
+--------------------------------------------------------+

The specific case actions are as follows:

The code is 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 ';

MySQL Query SQL statements today, yesterday, nearly 7 days, nearly 30 days, this month, and last January