Mysql query data today, yesterday, last 7 days, last 30 days, last month, and last month

Mysql query today, yesterday, last 7 days, last 30 days, this month, the data method of the last month said there is an article table article, storing the time for adding an article to the article is the add_time field, this field is of the int (5) type. to query the total number of articles added today and sort by time, the query statement is as follows :? 1select * from 'Article' wher

Mysql query today, yesterday, last 7 days, last 30 days, this month, the data method of the last month said there is an article table article, storing the time for adding an article to the article is the add_time field, this field is of the int (5) type. to query the total number of articles added today and sort by time, the query statement is as follows :? 1 select * from 'Article' wher

Mysql query data method today, yesterday, last 7 days, last 30 days, last 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:

?

1 select * from 'Article' where date_format (from_UNIXTIME ('add _ Time'), '% Y-% m-% D') = date_format (now (), '% Y-% m-% D ');

Or:

?

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:

?

1 select * from 'Article' where to_days ('add _ Time') = to_days (now ());

Query yesterday's information records:

?

1 select * from 'Article' where to_days (now ()-to_days ('add _ Time') <= 1;

Query Information records in the last seven days:

?

1 select * from 'Article' where date_sub (curdate (), INTERVAL 7 DAY) <= date ('add _ Time ');

Query the information records of the last 30 days:

?

1 select * from 'Article' where date_sub (curdate (), INTERVAL 30 DAY) <= date ('add _ Time ');

Query Information records for this month:

?

1 select * from 'Article' where date_format ('add _ time', '% Y % m') = date_format (curdate (),' % Y % m ');

Query the information records of the last month:

?

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:

?

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:

?

1 mysql> select to_days ('2017-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:

?

1 mysql> select str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % s ');

2?

3 + -------------------------------------------------------- +

4 | str_to_date ("14:39:51", '% Y-% m-% d % H: % I: % s') |

5 + -------------------------------------------------------- +

6 | 14:39:51? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

7 + -------------------------------------------------------- +

The procedure is as follows:

?

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 ';