MySQL query time basic tutorial-Mysql Tutorial-php.cn

MySQL query time basic tutorial

小云云

Release： 2017-11-29 11:35:14

Original

1592 people have browsed it

Perhaps we will have a headache during development when we need to count various report data. MySQL statements are very long and complex, so there is always a solution. Now I will share with you some basic SQL information. Statistical knowledge of time.

Now assume that there is such an order data table:

CREATE TABLE `order` (    
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,    
`order_sn` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39; COMMENT &#39;订单编号，保证唯一&#39;,    
`create_at` int(11) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;创建时间&#39;,    
 `success_at` int(11) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;订单完成时间&#39;,    
 `creator_id` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39; COMMENT &#39;订单创建人&#39;,    
PRIMARY KEY (`id`),    
UNIQUE KEY `uni_sn` (`order_sn`),    
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=&#39;订单表&#39;;

Copy after login

Now take the above table as an example to query related data:

Query all completed order numbers today:

SELECT `order_sn` FROM `order` WHERE YEARWEEK(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;)) = date_format(now(),&#39;%Y-%m-%d&#39;);

Copy after login

Query all completed order numbers for the current week:

SELECT `order_sn` FROM `order` WHERE YEARWEEK(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;)) = YEARWEEK(now());

Copy after login

Query all completed order numbers for last week:

SELECT `order_sn` FROM `order` WHERE YEARWEEK(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;)) = YEARWEEK(now())-1;

Copy after login

Query all completed order numbers for the current month :

select `order_sn` from `order` where FROM_UNIXTIME(success_at,&#39;%Y-%m&#39;)=date_format(now(),&#39;%Y-%m&#39;);

Copy after login

Query all completed order numbers in the last month:

select `order_sn` from `order` where FROM_UNIXTIME(success_at,&#39;%Y-%m&#39;)=date_format(DATE_SUB(curdate(), INTERVAL 1 month),&#39;%Y-%m&#39;);

Copy after login

Query all completed order numbers 6 months from now:

select `order_sn` from `order` where FROM_UNIXTIME(success_at,&#39;%Y-%m-%d %H:%i:%s&#39;) between date_sub(now(),interval 6 month) and now();

Copy after login

Query all orders in this quarter Completed order number:

select `order_sn` from `order` where QUARTER(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;))=QUARTER(now());

Copy after login

Query all completed order numbers in the previous quarter:

select `order_sn` from `order` where QUARTER(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;))=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

Copy after login

Query all completed order numbers this year:

select `order_sn` from `order` where YEAR(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;))=YEAR(NOW());

Copy after login

Query all completed order numbers in the previous year:

select `order_sn` from `order` where year(FROM_UNIXTIME(success_at,&#39;%Y-%m-%d&#39;))=year(date_sub(now(),interval 1 year));

Copy after login

The above content is a tutorial on querying report time in MySQL. I hope it will be helpful to everyone.

Related recommendations:

MySQL query time related knowledge

MySql query time period method sample code

MySQL methods and functions for querying time and date

The above is the detailed content of MySQL query time basic tutorial. For more information, please follow other related articles on the PHP Chinese website!