mysql中时间节点的_MySQL中常用到的关于时间的SQL

-- 今天

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS dayStart;

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS dayEnd;

-- 昨天

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS yesterdayStart;

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS yesterdayEnd;

-- 上周

SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS lastWeekStart

SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS lastWeekEnd;

-- 本周

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS weekStart;

SELECT DATE_FORMAT(DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS weekEnd;

-- 上月

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS lastMonthStart;

SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS lastMonthEnd;

-- 本月

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AS monthStart;

SELECT DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS monthEnd;

--获取今天时间年月日

--SELECT curdate() as nowDate

-- 获取现在时间(年月日时分秒)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') as nowTime

--获取本月有多少天

select DATEDIFF(date_add(curdate() - day(curdate()) + 1, interval 1 month),

DATE_ADD(curdate(), interval -day(curdate()) + 1 day)) as day

from dual;

获取某个月的每一天或当前月的每一天日期

此SQL参考自 https://blog.csdn.net/qq_38798251/article/details/89140471

SELECT date

FROM (select DATE_ADD(t1.dayed, INTERVAL t2.number DAY) date

from (select DATE_ADD(curdate(), interval -day(curdate()) + 1 day) dayed) t1,

(SELECT @xi := @xi + 1 as number

from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,

(SELECT 1

UNION

SELECT 2

UNION

SELECT 3

UNION

SELECT 4

UNION

SELECT 5

UNION

SELECT 6

UNION

SELECT 7

UNION

SELECT 8) xc2,

(SELECT @xi := -1) xc0) t2

where DATE_ADD(t1.dayed, INTERVAL t2.number DAY)) time

WHERE SUBSTRING(date, 1, 7) = SUBSTRING(CURDATE(), 1, 7)

另有一些未用到的SQL 做个记录

1、查询当前时间3天前的时间点

select date_sub(now() ,interval 3 day)

2.查询当前时间的时间3天之后的时间点

select data_sub(now(),interval -3 day)

3.查询当前时间3个月之前的时间点

select data_sub(now(),interval 3 month)

4.查询当前时间3个月之后的时间点

select data_sub(now,interval -3 month)