Knowledge about MySQL query time-PHP Tutorial-php.cn

Knowledge about MySQL query time

小云云

Release： 2023-03-17 15:24:01

Original

1480 people have browsed it

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 &#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

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!