MySQL Query time related knowledge

Sometimes programmers must be inseparable from some of the time statistics of the development work, the need to count the various report data, we will inevitably use MySQL to query the time. MySQL statements are very long and ultra-complex, so the small series for you to summarize some of the comparative basis of SQL on the statistical knowledge of time.

Now suppose you have such an order data sheet:

CREATE TABLE ' order ' (    ' id ' int (one) unsigned not NULL auto_increment,    ' order_sn ' varchar () CHARACTER SET UTF8 NO T null default ' COMMENT ' order number, guaranteed unique ',    ' create_at ' int (one) not NULL default ' 0 ' COMMENT ' creation time ',    ' success_at ' int (1 1) NOT null default ' 0 ' COMMENT ' Order completion time ',    ' creator_id ' varchar (CHARACTER SET UTF8 NOT null default ' ' COMMENT ' order creation Build person ',    PRIMARY key (' id '),    UNIQUE key ' uni_sn ' (' order_sn '),     engine=innodb auto_increment=1 DEFAULT CHARSET =UTF8MB4 collate=utf8mb4_unicode_ci comment= ' order form ';

Now, for example, query the relevant data as in the table above:

Check all the completed order numbers today:

SELECT ' order_sn ' from ' Order ' WHERE Yearweek (From_unixtime (success_at, '%y-%m-%d ') "= Date_format (now (), '%y-%m-%d ');

Query all completed order numbers for the current week:

SELECT ' order_sn ' from ' Order ' WHERE Yearweek (From_unixtime (success_at, '%y-%m-%d ') ") = Yearweek (now ());

Check all completed order numbers for the last week:

SELECT ' order_sn ' from ' Order ' WHERE Yearweek (From_unixtime (success_at, '%y-%m-%d ') ") = Yearweek (now ())-1;

Query all completed order numbers for the current month:

Select ' Order_sn ' from ' Order ' where From_unixtime (success_at, '%y-%m ') =date_format (now (), '%y-%m ');

Query last month for all completed order numbers:

Select ' Order_sn ' from ' Order ' where From_unixtime (success_at, '%y-%m ') =date_format (Date_sub (Curdate (), INTERVAL 1 month), '%y-%m ');

Query the current 6-month completed order number:

Select ' Order_sn ' from ' Order ' where From_unixtime (Success_at, '%y-%m-%d%h:%i:%s ') between Date_sub (now (), Interval 6 Month) and now ();

Check all completed order numbers for the quarter:

Select ' Order_sn ' from ' Order ' where QUARTER (From_unixtime (success_at, '%y-%m-%d ') ") =quarter (now ());

Query all completed order numbers for the previous quarter:

Select ' Order_sn ' from ' Order ' where QUARTER (From_unixtime (success_at, '%y-%m-%d ')] =quarter (Date_sub (now (), Interval 1 QUARTER));

Check all completed order numbers for this year:

Select ' Order_sn ' from ' Order ' where year (From_unixtime (Success_at, '%y-%m-%d ') ") =year (now ());

Check all completed order numbers for the previous year:

Select ' Order_sn ' from ' Order ' where year (From_unixtime (Success_at, '%y-%m-%d ')) =year (Date_sub (now (), Interval 1)) ;

If you take over a project now, it's about MySQL query time, so hopefully this article will help you.