MySql时间处理及interval函数运⽤
MySql时间操作
1.interval的说明
1.1、当函数使⽤时，即interval()，为⽐较函数，如：interval(10,1,3,5,7); 结果4；
原理：10为被⽐较数，后⾯1,3,5,7为⽐较数，将后⾯四个依次与10⽐较，看后⾯数字组有多少个少于10，则返回其个数。前提是后⾯数字组为从⼩到⼤排列，否则返回结果0。
1.2、当关键词使⽤时，表⽰为设置时间间隔，常⽤在date_add()与date_sub()函数⾥，
如：interval 1 day ，解释为将时间间隔设置为1天。
2.语法⽰例：
2.1、今天社会实践日志
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d 00:00:00’) AS ‘今天开始’;
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d 23:59:59’) AS ‘今天结束’;
2.2、 昨天
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), ‘%Y-%m-%d 00:00:00’) AS ‘昨天开始’;
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), ‘%Y-%m-%d 23:59:59’) AS ‘昨天结束’;
2.3、本周
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), ‘%Y-%m-%d 00:00:00’) AS ‘本周⼀’;
SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;
2.4、 上周
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d 00:00:00’) AS ‘上周⼀’;
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), ‘%Y-%m-%d 23:59:59’) AS ‘上周末’;
2.5、 本⽉
SELECT DATE_FORMAT( CURDATE(), ‘%Y-%m-01 00:00:00’) AS ‘本⽉初’;
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), ‘%Y-%m-%d 23:59:59’) AS ‘本⽉末’;
2.6、 上⽉
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), ‘%Y-%m-01 00:00:00’) AS ‘上⽉初’;
SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), ‘%Y-%m-%d 23:59:59’) AS ‘上⽉末’;
2.7、 本年第⼀天
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY)或
concat(year(now()),’-01-01’)//当前年份的第⼀天
concat(year(now()),’-12-31’)//当前年份的最后⼀天
2.8、 ⽉份处理
本⽉第⼀天
lect date_add(curdate(), interval - day(curdate()) + 1 day);
今天是当⽉的第⼏天：SELECT DAYOFMONTH( NOW());
本⽉最后⼀天
lect last_day(curdate());
上⽉第⼀天
lect date_add(curdate()-day(curdate())+1,interval -1 month);
上⽉最后⼀天
贫血吃什么东西补血最快
lect last_day(date_sub(now(),interval 1 month));
腹泻能吃什么水果下⽉第⼀天
lect date_add(curdate()-day(curdate())+1,interval 1 month);
下⽉最后⼀天
lect last_day(date_sub(now(),interval -1 month));
本⽉天数
lect day(last_day(curdate()));
上⽉今天的当前⽇期
lect date_sub(curdate(), interval 1 month);
上⽉今天的当前时间 (时间戳)
lect unix_timestamp(date_sub(now(),interval 1 month));
获取当前时间与上⽉时间的天数
lect datediff(curdate(), date_sub(curdate(), interval 1 month));
2.9、 ⽇期处理
做油条----时间差换算
SELECT
TIME_TO_SEC(TIMEDIFF(‘2018-09-30 19:38:45’, ‘2018-08-23 10:13:01’)) AS DIFF_SECOND1, – 秒
UNIX_TIMESTAMP(‘2018-09-30 19:38:45’)-UNIX_TIMESTAMP(‘2018-08-23 10:13:01’) AS DIFF_SECOND2, –秒
神秘岛读书笔记TIMESTAMPDIFF(SECOND,‘2018-08-23 10:13:01’,‘2018-09-30 19:38:45’) AS DIFF_SECOND3, – 秒TIMESTAMPDIFF(MINUTE,‘2018-08-23 10:13:01’,‘2018-09-30 19:38:45’) AS DIFF_MINUTE, – 分TIMESTAMPDIFF(HOUR,‘2018-08-23 10:13:01’,‘2018-09-30 19:38:45’) AS DIFF_HOUR, – ⼩时TIMESTAMPDIFF(DAY ,‘2018-08-23 10:13:01’,‘2018-09-30 19:38:45’) AS DIFF_DATE1, – 天
DATEDIFF(‘2018-09-30 19:38:45’,‘2018-08-23 10:13:01’) AS DIFF_DATE2, – 天
TIMESTAMPDIFF(MONTH,‘2018-08-23 10:13:01’,‘2018-09-25 19:38:45’) AS DIFF_MONTH, – ⽉TIMESTAMPDIFF(YEAR,‘2018-08-23 10:13:01’,‘2020-07-25 19:38:45’) AS DIFF_YEAR – 年
FROM DUAL;
⽇期增加年，⽉，天，⼩时，分，秒
lect date_add(⽇期, interval 1 day); ⽇期加天红心红薯
lect date_add(⽇期, interval 1 hour); ⽇期加⼩时
lect date_add(⽇期, interval 1 minute); ⽇期加分
lect date_add(⽇期, interval 1 cond);⽇期加秒
lect date_add(⽇期, interval 1 microcond); ⽇期加微秒
lect date_add(⽇期, interval 1 week); ⽇期加周
lect date_add(⽇期, interval 1 month); ⽇期加⽉
lect date_add(⽇期, interval 1 quarter); ⽇期加季度
lect date_add(⽇期, interval 1 year); ⽇期加年
当⽉的所有天数循环（输⼊⽇期⾃动换算当⽉天数）
SELECT ADDDATE(y.first, x.d - 1) as d
FROM (
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
便多音字组词
SELECT 31
) x,
(
SELECT ‘2019-09-05’ - INTERVAL DAY(‘2019-09-05’) - 1 DAY AS first, DAY(LAST_DAY(‘2019-09-05’)) AS last
) y
WHERE x.d <= y.last
模拟12个⽉⽇期
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-01-01’),’%Y-%m-%d’)屋漏偏逢连夜雨
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-01-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-02-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-02-28’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-03-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-03-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-04-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-04-30’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-05-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-05-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-06-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-06-30’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-07-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-07-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-08-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-08-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-09-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-09-30’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-10-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-10-31’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-11-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-11-30’),’%Y-%m-%d’) END_V UNION
SELECT STR_TO_DATE(CONCAT(YEAR(now()),’-12-01’),’%Y-%m-%d’)
BEGIN_V,STR_TO_DATE(CONCAT(YEAR(now()),’-12-31’),’%Y-%m-%d’) END_V
⽇期普遍处理
当年第⼀天：
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
当年最后⼀天：
SELECT concat(YEAR(now()),’-12-31’);
当前week的第⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);
当前week的最后⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);
前⼀week的第⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY);
前⼀week的最后⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY);
前两week的第⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY);
前两week的最后⼀天：
lect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY);
