MySQL query time basic tutorial

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 '' COMMENT '订单编号，保证唯一',    
`create_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',    
 `success_at` int(11) NOT NULL DEFAULT '0' COMMENT '订单完成时间',    
 `creator_id` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '订单创建人',    
PRIMARY KEY (`id`),    
UNIQUE KEY `uni_sn` (`order_sn`),    
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

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,'%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());

Query all completed order numbers for 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 all completed order numbers in the last month:

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

Query all completed order numbers 6 months from now:

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();

Query all orders in this quarter Completed order number:

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

Query all completed order numbers in the previous quarter:

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

Query all completed order numbers this year:

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

Query all completed order numbers in the previous year:

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

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!