首页技术博客 技术经验 正文

mysql日期函数查询今天、昨天、上周,今年去年等数据!

4个月前 ( 04-04 ) 347 0条评论

mysql制作报表、统计等都常常需要用到日期函数和区间范围查询。那mysql怎么查询今天、昨天、近7天、近30天、本周、上周、本月、上月、本季度、上季度、今年、去年的数据呢?

查询应用

创建表和添加演示数据

CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(64) NOT NULL, -- 订单号
  `add_time` int(10) DEFAULT NULL, -- 时间戳
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 当前时间为2018-02-28 00:00:01
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('001',UNIX_TIMESTAMP('2017-12-08'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('002',UNIX_TIMESTAMP('2018-01-26'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('003',UNIX_TIMESTAMP('2018-02-05'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('004',UNIX_TIMESTAMP('2018-02-26'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('005',UNIX_TIMESTAMP('2018-02-27'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('006',UNIX_TIMESTAMP('2018-02-28'));
INSERT INTO `order`(`order_sn`,`add_time`) VALUES('007',UNIX_TIMESTAMP(now()));


注意:这里以时间戳为例。如果是datetime类型,以下示例请去掉FROM_UNIXTIME函数即可。


-- 今天
SELECT * FROM `order` WHERE TO_DAYS(FROM_UNIXTIME(`add_time`)) = TO_DAYS(now());
 
-- 昨天
SELECT * FROM `order` WHERE TO_DAYS(now()) - TO_DAYS(FROM_UNIXTIME(`add_time`)) = 1;
 
-- 近7天
SELECT * FROM `order` WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(FROM_UNIXTIME(`add_time`));
 
-- 近30天
SELECT * FROM `order` WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(FROM_UNIXTIME(`add_time`));
 
-- 本周
SELECT * FROM `order` WHERE YEARWEEK(DATE(FROM_UNIXTIME(`add_time`))) = YEARWEEK(now());
 
-- 上周
SELECT * FROM `order` WHERE YEARWEEK(now()) - YEARWEEK(DATE(FROM_UNIXTIME(`add_time`))) = 1;
 
-- 本月
SELECT * FROM `order` WHERE DATE_FORMAT(FROM_UNIXTIME(`add_time`),'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
 
-- 上月
SELECT * FROM `order` WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'), DATE_FORMAT(FROM_UNIXTIME(`add_time`),'%Y%m')) = 1;
 
-- 本季度
SELECT * FROM `order` WHERE QUARTER(FROM_UNIXTIME(`add_time`)) = QUARTER(now());
 
-- 上季度
SELECT * FROM `order` WHERE QUARTER(FROM_UNIXTIME(`add_time`)) = QUARTER(DATE_SUB(now(),INTERVAL 1 QUARTER));
 
-- 今年
SELECT * FROM `order` WHERE YEAR(FROM_UNIXTIME(`add_time`)) = YEAR(now())
 
-- 去年
SELECT * FROM `order` WHERE YEAR(FROM_UNIXTIME(`add_time`)) = YEAR(DATE_SUB(now(),INTERVAL 1 YEAR));



日期函数

以上示例应用到的日期函数


TO_DAYS(date) 


给定一个日期date, 返回一个天数 (从年份0开始的天数 )。 


SELECT TO_DAYS(now());

-- 737118


UNIX_TIMESTAMP(date) 


它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回,即时间戳。


SELECT UNIX_TIMESTAMP(now());

-- 1519747201



FROM_UNIXTIME(unix_timestamp) 


将时间戳转时间格式,如:返回'YYYY-MM-DD HH:MM:SS'


SELECT FROM_UNIXTIME(1519755184);

-- 2018-02-28 00:00:01



DATE_SUB(date,INTERVAL expr type) 


函数从日期减去指定的时间间隔。


SELECT DATE_SUB(now(),INTERVAL 1 DAY);

-- 2018-02-27 00:00:01



CURDATE() 


将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回。


SELECT CURDATE();

-- 2018-02-28



DATE(expr) 


提取日期或时间日期表达式expr中的日期部分。


SELECT DATE(now());

-- 2018-02-28



DATE_FORMAT(date,format) 


根据format 字符串安排date 值的格式。


SELECT DATE_FORMAT(now(),'%Y-%m');

-- 2018-02



YEARWEEK(date) 


返回一个日期对应的年或周


SELECT YEARWEEK(now());

-- 201808



PERIOD_DIFF(P1,P2) 


返回周期 P1 和 P2 之间的月份数,P1 和P2 的格式应该为YYMM或YYYYMM


SELECT PERIOD_DIFF('201802', '201801')

-- 1



QUARTER(date) 


返回date 对应的一年中的季度值,范围是从 1到 4


SELECT QUARTER(now())

-- 1



YEAR(date) 


返回date 对应的年份,范围是从1000到9999。


SELECT YEAR(now())

-- 2018





文章版权及转载声明

本文作者:符文浩 网址:http://blog.haozi.org/post/443.html发布于 4个月前 ( 04-04 )
文章转载或复制请以超链接形式并注明出处。

发表评论

评论列表 (暂无评论,347人围观)参与讨论

还没有评论,来说两句吧...

取消
支付宝二维码
支付宝二维码
微信二维码