SQL statements get today, yesterday, nearly 7 days, this week, last week, this month, last month, half year data

 onTo say there is an article table article, the time to store an article's added article is the Add_time field, which is an int (5) type, you now 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: Geneva     Geneva    1    Select* from' Article 'whereDate_format (From_unixtime (' Add_time '),'%y-%m-%d') = Date_format (now (),'%y-%m-%d');Genevaor: to      .    1    Select* from' Article 'whereTo_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: ,      thequery today's information records:Ten      One    1    Select* from' Article 'whereTo_days (' add_time ') =To_days (now ()); Aquery yesterday's information record: -      -    1    Select* from' Article 'whereTo_days (now ()) –to_days (' Add_time ') <=1; theCheck the information record for nearly 7 days: -      -    1    Select* from' Article 'whereDate_sub (Curdate (), INTERVAL7Day) <=date (' Add_time '); -Check the information record for nearly 30 days: +      -    1    Select* from' Article 'whereDate_sub (Curdate (), INTERVAL -Day) <=date (' Add_time '); +query this month's information records: A      at    1    Select* from' Article 'whereDate_format (' Add_time ', '%y%m') = Date_format (Curdate (), '%y%m'); -Query the information record for the previous January: -      -    1    Select* from' Article 'wherePeriod_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: -      in（1) To_days -      tolike its name, it is a Unix timestamp that converts a specific date or time string to a day, such as: +      -     onMysql>SelectTo_days ('2010-11-22 14:39:51');  the     Geneva+--------------------------------+ *    Geneva| To_days ('2010-11-22 14:39:51') | $    Geneva+--------------------------------+Panax Notoginseng     to|734463| -     .+--------------------------------+ the     -    +     ,Mysql>SelectTo_days ('2010-11-23 14:39:51'); A     the+--------------------------------+ the    Ten| To_days ('2010-11-23 14:39:51') | +     One+--------------------------------+ -     A|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.  -      -Reminders: the      -（1) To_days () is not used for solar1582because the missing date is not taken into account when the calendar changes. Therefore, the results of this function are not reliable for dates prior to 1582 (perhaps in other regions for the next year). Wuyi       the（2) MySQL"date and Time type"The rule in is to convert the two-digit year value in a date to four-bit. So for'1997-10-07'And'97-10-07'will be treated as the same date: -      Wu    1Mysql>SelectTo_days ('1997-10-07'), To_days ('97-10-07'); -    2     About    3-729669,729669 $（2) Str_to_date -      -This function can translate the string time completely, such as: -      A    1Mysql>SelectStr_to_date ("2010-11-23 14:39:51",'%y-%m-%d%h:%i:%s'); +    2     the    3+--------------------------------------------------------+ -    4| Str_to_date ("2010-11-23 14:39:51",'%y-%m-%d%h:%i:%s') | $    5+--------------------------------------------------------+ the    6| .- One- at  -: the:Wuyi| the    7+--------------------------------------------------------+ theThe specific case actions are as follows: the      -    1    SelectStr_to_date (article. ' Add_time '),'%y-%m-%d%h:%i:%s') in    2     fromarticle the    3    whereStr_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';

Query TodaySelect* fromTable namewhereTo_days (Time field name) =To_days (now ()); Yesterday SELECT* FROM table name WHERE to_days (now ()) –to_days (Time field name) <=17-Day SELECT* FROM table namewhereDate_sub (Curdate (), INTERVAL7Day) <=Date (Time field name) nearly 30 days SELECT* FROM table namewhereDate_sub (Curdate (), INTERVAL -Day) <=Date (Time field name) this month SELECT* FROM table name where Date_format (Time field name, '%y%m ') = Date_format (Curdate (), '%Y%m ') last month SELECT* FROM table name WHERE Period_diff (Date_format (now (), '%y%m '), Date_format (Time field name, '%y%m ')) =1at the same time, attach a MySQL official related document# query this quarter dataSelect* from' Ht_invoice_information 'whereQUARTER (create_date) =QUARTER (now ()); #查询上季度数据Select* from' Ht_invoice_information 'whereQUARTER (create_date) =quarter (Date_sub (now (), interval1QUARTER)); #查询本年数据Select* from' Ht_invoice_information 'whereYear (create_date) =Year (now ()); #查询上年数据Select* from' Ht_invoice_information 'whereYear (create_date) =year (Date_sub (now (), interval1Year )); Query data for the current week SELECT Name,submittime from Enterprise WHERE yearweek (Date_format (Submittime,'%y-%m-%d')) =Yearweek (now ()); query last week's data select Name,submittime from Enterprise WHERE yearweek (Date_format (Submittime,'%y-%m-%d')) = Yearweek (now ())-1; query data for the current monthSelectName,submittime fromEnterprisewhereDate_format (Submittime,'%y-%m') =date_format (now (),'%y-%m') query data that is currently 6 months from nowSelectName,submittime fromEnterprisewhereSubmittime between Date_sub (now (), interval6Month ) and now ();SelectName,submittime fromEnterprisewhereDate_format (Submittime,'%y-%m') =date_format (Date_sub (Curdate (), INTERVAL1MONTH),'%y-%m')Select* from' User 'whereDate_format (Pudate,'%y%m') = Date_format (Curdate (),'%y%m' ) ;Select* fromUserwhereWeekOfYear (From_unixtime (Pudate,'%y-%m-%d')) =WeekOfYear (now ())Select* fromUserwhereMONTH (From_unixtime (Pudate,'%y-%m-%d')) =MONTH (now ())Select* from[user]whereYear (From_unixtime (Pudate,'%y-%m-%d')) =Year (now ()) and MONTH (From_unixtime (Pudate,'%y-%m-%d')) =MONTH (now ())Select* from[user]wherepudate between last month and first day of next monthwhereDate (regdate) =curdate ();Select* fromTestwhereYear (regdate) =year (now ()) and month (regdate) =month (now ()) and day (regdate) =Day ( today ()) SELECT date (c_instime), curdate () from ' T_score ' WHERE1LIMIT0, -

SQL statements get today, yesterday, nearly 7 days, this week, last week, this month, last month, half year data