Mysql query statement on the current day, yesterday, last 7 days, last 30 days, this month, and last month

I used to query the database today or yesterday, for the last seven days, for the last 30 days, and for the last month, the data was directly determined through php. This is an additional step, next I will introduce an article that uses SQL statements to calculate the data statements of the current day, yesterday, last 7 days, last 30 days, this month, and last month.

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:

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

The Code is as follows:	Copy code
1 select * from 'Article' where to_days ('add _ Time') = to_days (now ());

Query yesterday's information records:

The Code is as follows:	Copy code
1 select * from 'Article' where to_days (now ()-to_days ('add _ Time') <= 1;

Query Information records in the last seven days:

The Code is as follows:	Copy code
1 select * from 'Article' where date_sub (curdate (), INTERVAL 7 DAY) <= date ('add _ Time ');

Query the information records of the last 30 days:

The Code is as follows:	Copy code
1 select * from 'Article' where date_sub (curdate (), INTERVAL 30 DAY) <= date ('add _ Time ');

Query Information records for this month:

The Code is as follows:	Copy code
1 select * from 'Article' where date_format ('add _ time', '% Y % m') = date_format (curdate (),' % Y % m ');

Query the information records of the last month:

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

The Code is as follows:	Copy code
01 mysql> select to_days ('2017-11-22 14:39:51 ′); 02 + ----------- + 03 | to_days ('2017-11-22 14:39:51 ') | 04 + ----------- + 05 | 734463 | 06 + ----------- + 07 08 mysql> select to_days ('2017-11-23 14:39:51 ′); 09 + ----------- + 10 | to_days ('2017-11-23 14:39:51 ') | 11 + ----------- + 12 | 734464 | 13 + ----------- +

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:

The Code is as follows:	Copy code
1 mysql> select to_days ('1970-10-07'), to_days ('97-10-07 ′); 2 3-> 729669,729 669 (2) str_to_date

This function can completely translate the string time, for example:

The Code is as follows:	Copy code
1 mysql> select str_to_date ("2010-11-23 14:39:51", '% Y-% m-% d % H: % I: % s '); 2 3 + ------------------- + 4 | str_to_date ("2010-11-23 14:39:51", '% Y-% m-% d % H: % I: % s') | 5 + ------------------- + 6 | 14:39:51 | 7 + ------------------- +

The procedure is as follows:

The Code is as follows:	Copy code
1 select str_to_date (article. 'add _ time', '% Y-% m-% d % H: % I: % s ') 2 from article 3 where str_to_date (article. 'Add _ time', '% Y-% m-% d % H: % I: % s')> = '2017-06-28 08:00:00' and str_to_date (article. 'Add _ time', '% Y-% m-% d % H: % I: % s') <= '2017-06-28 09:59:59 ′;