Knowledge about MySQL query time

Sometimes programmers must be inseparable from some development work on time statistics and need to count various report data. We will inevitably use MySQL to query time. MySQL statements are all very long and complex, so I will summarize some basic SQL statistical knowledge about time for you.

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

If you are taking over a project now, which is about MySQL query time, then I hope this article can help you.

Related recommendations:

The relationship between PHP and MySQL

MySQL time and date query methods and functions

mysql time function uses PHP code for mysql database difference comparison

mysql time storage related issues

mysql time operation function Usage summary

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