Home > Database > Mysql Tutorial > body text

MySQL query time basic tutorial

小云云
Release: 2017-11-29 11:35:14
Original
1634 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 '' 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='订单表';
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,'%Y-%m-%d')) = date_format(now(),'%Y-%m-%d');
Copy after login

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());
Copy after login

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;
Copy after login

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');
Copy after login

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');
Copy after login

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();
Copy after login

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());
Copy after login

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));
Copy after login

Query all completed order numbers this year:

select `order_sn` from `order` where YEAR(FROM_UNIXTIME(success_at,'%Y-%m-%d'))=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,'%Y-%m-%d'))=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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template