MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, last month SQL statement

Source: Internet
Author: User
Tags mysql query

Table article, the time to store the article added is the Add_time field, which is type int (5) and 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:

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 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:


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;
Inquire about the information records of the last 7 days:


1 SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL 7 day) <= date (' Add_time ');
Inquire about the information records of the last 30 days:


1 SELECT * from ' article ' where Date_sub (Curdate (), INTERVAL Day) <= date (' Add_time ');
Check this month's information record:


1 SELECT * from ' article ' where Date_format (' add_time ', '%y%m ') = Date_format (Curdate (), '%y%m ');
Check the information records of the previous January:


1 SELECT * from ' article ' where Period_diff (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:


Mysql> Select To_days (' 2010-11-22 14:39:51 ');

+--------------------------------+

03 | To_days (' 2010-11-22 14:39:51 ') |

+--------------------------------+

05 | 734463 |

+--------------------------------+

07

Mysql> Select To_days (' 2010-11-23 14:39:51 ');

+--------------------------------+

10 | To_days (' 2010-11-23 14:39:51 ') |

One +--------------------------------+

12 | 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:


1 mysql> Select To_days (' 1997-10-07 '), To_days (' 97-10-07 ');

2

3-> 729669, 729669
(2) Str_to_date

This function can completely translate the string time, such as:


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 | 2010-11-23 14:39:51 |

7 +--------------------------------------------------------+
Specific case actions are 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 ') >= ' 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 ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.