MySQL-日期相关查询语句

1、常见的日期相关的sql

-- 日期字符串转时间戳，10位，数据库存储的是13位，记得除以1000
select UNIX_TIMESTAMP('2019-09-01 00:00:00');

-- 时间戳转日期
select FROM_UNIXTIME(1567267200,'%Y-%m-%d %H:%i:%s')

查询当天，格式为YYYY-MM-DD HH:mm:ss　　

SELECT NOW();  -- 2015-09-28 13:42:00

查询当天0点，格式为YYYY-MM-DD HH:mm:ss　　

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');  -- 2015-09-28 00:00:00

查询当天早上9点，格式为YYYY-MM-DD HH:mm:ss

SELECT DATE_ADD(CURDATE(), INTERVAL 9 HOUR);  -- 2015-09-28 09:00:00

查询昨天，格式为YYYY-MM-DD

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);  -- 当天为3月4号，昨天即为 3月3号

查询前天，

SELECT date_sub(curdate(), interval 2 day) '前天';

查询昨天开始，往前数7天的日期为

SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);  -- 当天为3月4号，2月28天，返回的日期为2月25号

查询本周一

select date_sub(curdate(), interval weekday(curdate()) day) '本周一';

查询本周日

select date_sub(curdate(), interval weekday(curdate()) -6 day) '本周日';

查询上周 周一零点

SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d %H:%i:%s' )

查询上周一
select date_sub(curdate(), interval weekday(curdate()) + 7 day) '上周一';

查询上周日

select date_sub(curdate(), interval weekday(curdate()) + 1 day) '上周日';

查询上周 周日24点
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 24:00:00')

查询 上上周一
select date_sub(curdate(), interval weekday(curdate()) + 14 day) '上上周一';
查询上上周日
select date_sub(curdate(), interval weekday(curdate()) + 8 day) '上上周日';

查询本月

select date_format(curdate(), '%Y%m') '本月';

查询上月 1号零点

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 %H:%i:%s')
查询上月 最后一天24点

SELECT DATE_FORMAT(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month), '%Y-%m-%d 24:00:00') as date;

查询上月最后一天

SELECT DATE_FORMAT(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month), '%Y-%m-%d') as date;

查询上上个月 第一天
select date_format(DATE_ADD(CURDATE(), INTERVAL -2 MONTH),"%Y%m01");

查询上上个月 最后一天

SELECT date_format(DATE_SUB(DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m01'), INTERVAL 1 DAY),"%Y%m%d");

#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

2、常用的日期函数

(1) WEEKDAY(date)  

MySQL中，可以使用WEEKDAY函数来判断给定日期是星期几。WEEKDAY函数返回的值是0~6，0表示星期一，1表示星期二，以此类推，6表示星期日。因此，可以通过判断WEEKDAY返回的值是否在0~4之间来判断当前日期是否是工作日。

(2) YEAR(date)

返回日期对应的年

(3) 对日期时间进行加减运算

ADDDATE(date,interval expr type)

DATE_ADD(date,interval expr type)     例如：查询今天九点  SELECT DATE_ADD(  CURDATE(),   INTERVAL 9 HOUR );   

SUBDATE(date,interval expr type)

DATE_SUB(date,interval expr type)     例如：查询昨天       SELECT DATE_SUB(  CURDATE(),  INTERVAL 1 DAY )

其中，date是一个datetime或date值；expr是对date进行加减法的一个表达式字符串或一个数字；type指明表达式expr应该如何被解释，是减去1天还是一年等。

(4) CURDATE()  返回当前日期，默认格式是'yyyy-mm-dd'

例如：SELECT CURDATE()     -- 执行结果：2023-07-24

(5).CURTIME()  返回当前时间点 ，默认格式是'hh:mm:ss'

例如：SELECT CURTIME()     -- 执行结果：15:01:25

(6).NOW()  -- 返回当前时间，默认格式是 'yyyy-mm-dd hh:mm:ss'

例如： select NOW();   -- 执行结果为 2023-07-24 14:55:57

(7).TIMESTAMPDIFF(type,expr1,expr2)  

返回起始日expr1和结束日expr2之间的时间差整数。

时间差的单位由type指定：

second 秒  minute 分 hour 时 day 天 month 月 year 年

例如： SELECT TIMESTAMPDIFF(day,'2023-07-20','2023-07-24')   -- 执行结果  4例如： SELECT TIMESTAMPDIFF(hour,'2023-07-24 09:00:00','2023-07-24 15:00:00')   -- 执行结果  6

(8).UNIX_TIMESTAMP([date])  -- date 转为时间戳，默认为秒

例如： select unix_timestamp('2023-07-24');  -- 1690128000  是从 1970-01-01 00:00:00 到2023-07-24 当前时间的秒数；

(9).FROM_UNIXTIME(unix_timestamp)  -- 时间戳转为时间，默认格式为：'yyyy-mm-dd hh:mm:ss'

例如： select from_unixtime(1690181610);   -- 2023-07-24 14:53:30

(10).某日期是工作日还是非工作日，可使用函数 

DAYOFWEEK() : 在MySQL中，DAYOFWEEK() 是一个函数，它返回一个数字，表示给定日期是一周中的第几天。这个数字的范围是1到7，其中1代表星期日，2代表星期一，以此类推，7代表星期六。

例如： SELECT DAYOFWEEK('2023-10-23') as DayOfWeek;   -- 查询某日期对应是星期几，也可以传日期字段，输出星期几

WEEKDAY()：在MySQL中，WEEKDAY() 是一个函数，它返回一个数字，表示给定日期是一周中的第几天。这个数字的范围是0到6，其中0代表星期一，1代表星期二，以此类推，6代表星期日。

例如：SELECT WEEKDAY('2023-10-23') as DayOfWeek;    -- 查询某日期对应是星期几。

以上如有任何有疑问的，欢迎随时找我沟通哦。