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

2022-06-19 0 416

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

收藏 (0) 打赏

感谢您的支持,我会继续努力的!

打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
点赞 (0)

黑龙江省声美网络科技有限公司 技术经验 mysql日期函数查询今天、昨天、上周,今年去年等数据! http://www.fuwenhao.com/311.html

常见问题

相关文章

发表评论
暂无评论
官方客服团队

为您解决烦忧 - 24小时在线 专业服务