mysql日期函数查询今天、昨天、上周等

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

原创文章，转载请注明出处：https://www.weizhixi.com/article/77.html