MySql相关时间处理的函数

烟火缠过客

已于 2023-09-26 10:16:53 修改

阅读量1k

点赞数

分类专栏： MySQL 文章标签： mysql java 数据库

于 2022-11-11 16:55:12 首次发布

本文链接：https://blog.csdn.net/LuckFairyLuckBaby/article/details/127808947

版权

MySQL 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

1.介绍

时间的间隔
时间格式的转换

2.时间间隔

计算两日期时间之间相差的天数，秒数，分钟数，周数，小时数，这里主要分享的是通过MySql内置的函数 TimeStampDiff() 实现。函数 TimeStampDiff() 是MySQL本身提供的可以计算两个时间间隔的函数，语法为：

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

返回日期或日期时间表达式 datetime_expr1 和datetime_expr2the 之间的整数差
datetime_expr1:起始日期
datetime_expr2:结束日期
unit:间隔单位

unit单位有以下几种

microseconds:表示间隔是毫秒
SECOND:间隔是秒
MINUTE:分钟
HOUR:小时
DAY:天
WEEK:周
MONTH:月
QUARTER:季度
YEAR :年

3.示例

-- 秒
select TIMESTAMPDIFF(second,'2022-11-10 12:30:30','2022-11-10 12:40:36') as '耗时(秒)';
-- 分钟
select TIMESTAMPDIFF(minute,'2022-11-10 12:30:30','2022-11-10 12:50:36') as '耗时(分钟)';
-- 小时
select TIMESTAMPDIFF(hour,'2022-11-10 12:30:30','2022-11-10 15:50:36') as '耗时(小时)';
-- 天
select TIMESTAMPDIFF(day,'2022-11-10 12:30:30','2022-11-13 15:50:36') as '耗时(天)';
-- 周
select TIMESTAMPDIFF(week,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(周)';
-- 月
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(月)';
-- 季度
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(季度)';
-- 年
select TIMESTAMPDIFF(year,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(年)';

3.1 计算两个日期相差多少秒

-- 秒
select TIMESTAMPDIFF(second,'2022-11-10 12:30:30','2022-11-10 12:40:36') as '耗时(秒)';

3.2 计算两个日期相差多少分钟

-- 分钟
select TIMESTAMPDIFF(minute,'2022-11-10 12:30:30','2022-11-10 12:50:36') as '耗时(分钟)';

3.3 计算两个日期相差多少小时

-- 小时
select TIMESTAMPDIFF(hour,'2022-11-10 12:30:30','2022-11-10 15:50:36') as '耗时(小时)';

3.4 计算两个日期相差多少天

-- 天
select TIMESTAMPDIFF(day,'2022-11-10 12:30:30','2022-11-13 15:50:36') as '耗时(天)';

3.5 计算两个日期相差多少周

-- 周
select TIMESTAMPDIFF(week,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(周)';

3.6 计算两个日期相差多少月

-- 月
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(月)';

3.7 计算两个日期相差多少季度

-- 季度
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(季度)';

3.8 计算两个日期相差多少年

-- 年
select TIMESTAMPDIFF(year,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(年)';

3.9 补充

另外计算两日期或时间之间相差多少天还可以使用 to_days 函数，但是该函数不用于阳历出现(1582)前的值，原因是当日历改变时，遗失的日期不会被考虑在内。因此对于1582 年之前的日期(或许在其它地区为下一年 ), 该函数的结果实不可靠的。具体用法如：

计算天的另一种函数TO_DAYS(TIME)

-- 天
select to_days('2022-11-13 15:50:36') - to_days('2022-11-10 12:30:30') as '耗时(天)';

计算秒的另一种函数UNIX_TIMESTAMP(TIME)

-- 秒
select unix_timestamp('2022-11-10 12:40:36') - unix_timestamp('2022-11-10 12:30:30')  as '耗时(秒)';

计算分钟的另一个函数SEC_TO_TIME(TIME)

-- 分钟
select sec_to_time(unix_timestamp('2022-11-10 12:50:36') - unix_timestamp('2022-11-10 12:30:30'))  as '耗时(分钟)';

4.时间函数

4.1 NOW():用于获取当前日期和时间函数

-- 当前日期
select now() as '当前日期和时间' from dual;

4.2 CURDATE():用于获取当前日期，同CURRENT_DATE()

-- 用于获取当前日期
select curdate() as '当前日期' from dual;

4.3 CURTIME():用于获取当前时间，同CURRENT_TIME()

-- 用于获取当前时间，同CURRENT_TIME()
select curtime() as '当前时间' from dual;

4.4 CURRENT_TIMESTAMP():用于获取当前日期和时间函数

-- 用于获取当前日期和时间函数
select CURRENT_TIMESTAMP() from dual;

4.5 DATE()函数，用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY-mm-dd"

-- 用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY-mm-dd"
select date('2022-11-11 15:22:10');

4.6 YEAR()函数，用于提取时间字段的年，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY"

-- 用于提取时间字段的年，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY"
select year('2022-11-11 15:22:10');

4.7 MONTH()函数，用于提取时间字段的月，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"mm"，若月份前有0会忽略

-- 用于提取时间字段的月，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"mm"，若月份前有0会忽略
select month('2022-11-11 15:22:10');

4.8 DAY()函数，用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"dd"，若日期前有0会忽略

-- 用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"dd"，若日期前有0会忽略
select day('2022-11-11 15:22:10');

4.9 TIME()函数，用于提取时间字段的时间，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH:ii:ss"

-- 用于提取时间字段的时间，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH:ii:ss"
select time('2022-11-11 15:22:10');

4.10 HOUR()函数，用于提取时间字段的小时，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH"，若小时前有0会忽略，如08则返回8

-- 用于提取时间字段的小时，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH"，若小时前有0会忽略，如08则返回8
select hour('2022-11-11 15:22:10');

4.11 MINUTE()函数，用于提取时间字段的分钟，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ii"，若分钟前有0会忽略，如08则返回8

-- 用于提取时间字段的分钟，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ii"，若分钟前有0会忽略，如08则返回8
select minute('2022-11-11 15:22:10');

4.12 SECOND()函数，用于提取时间字段的秒，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ss"，若秒前有0会忽略，如08则返回8

-- 用于提取时间字段的秒，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ss"，若秒前有0会忽略，如08则返回8
select second('2022-11-11 15:22:10');

4.13 UNIX_TIMESTAMP()函数，获取当前时间戳，还可以将时间字段转换成时间戳

-- 获取当前时间戳，还可以将时间字段转换成时间戳
select unix_timestamp();
select unix_timestamp('2022-11-11 15:22:10');

5 时间格式化函数

5.1 DATE_FORMAT()和TIME_FORMAT()功能相同

-- DATE_FORMAT()和TIME_FORMAT()功能相同
select date_format(now(),'%Y-%m-%d %H:%i:%s'); 
select time_format(now(),'%Y-%m-%d %H:%i:%s');
select date_format(now(),'%Y-%m-%d'); 
select time_format(now(),'%Y-%m-%d');

6.本次测试所用所有sql

-- 秒
select TIMESTAMPDIFF(second,'2022-11-10 12:30:30','2022-11-10 12:40:36') as '耗时(秒)';
-- 分钟
select TIMESTAMPDIFF(minute,'2022-11-10 12:30:30','2022-11-10 12:50:36') as '耗时(分钟)';
-- 小时
select TIMESTAMPDIFF(hour,'2022-11-10 12:30:30','2022-11-10 15:50:36') as '耗时(小时)';
-- 天
select TIMESTAMPDIFF(day,'2022-11-10 12:30:30','2022-11-13 15:50:36') as '耗时(天)';
-- 周
select TIMESTAMPDIFF(week,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(周)';
-- 月
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2022-12-13 15:50:36') as '耗时(月)';
-- 季度
select TIMESTAMPDIFF(month,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(季度)';
-- 年
select TIMESTAMPDIFF(year,'2022-11-10 12:30:30','2023-12-13 15:50:36') as '耗时(年)';


-- 天
select to_days('2022-11-13 15:50:36') - to_days('2022-11-10 12:30:30') as '耗时(天)';
-- 秒
select unix_timestamp('2022-11-10 12:40:36') - unix_timestamp('2022-11-10 12:30:30')  as '耗时(秒)';
-- 分钟
select sec_to_time(unix_timestamp('2022-11-10 12:50:36') - unix_timestamp('2022-11-10 12:30:30'))  as '耗时(分钟)';


-- 当前日期
select now() as '当前日期和时间' from dual;
-- 用于获取当前日期
select curdate() as '当前日期' from dual;
-- 用于获取当前时间，同CURRENT_TIME()
select curtime() as '当前时间' from dual;
-- 用于获取当前日期和时间函数
select CURRENT_TIMESTAMP() from dual;
-- 用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY-mm-dd"
select date('2022-11-11 15:22:10');
-- 用于提取时间字段的年，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"YYYY"
select year('2022-11-11 15:22:10');
-- 用于提取时间字段的月，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"mm"，若月份前有0会忽略
select month('2022-11-11 15:22:10');
-- 用于提取时间字段的日期，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"dd"，若日期前有0会忽略
select day('2022-11-11 15:22:10');
-- 用于提取时间字段的时间，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH:ii:ss"
select time('2022-11-11 15:22:10');
-- 用于提取时间字段的小时，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"HH"，若小时前有0会忽略，如08则返回8
select hour('2022-11-11 15:22:10');
-- 用于提取时间字段的分钟，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ii"，若分钟前有0会忽略，如08则返回8
select minute('2022-11-11 15:22:10');
-- 用于提取时间字段的秒，例如t01的tdate为'2020-05-26 11:11:11'，返回的格式为"ss"，若秒前有0会忽略，如08则返回8
select second('2022-11-11 15:22:10');
-- 获取当前时间戳，还可以将时间字段转换成时间戳
select unix_timestamp();
select unix_timestamp('2022-11-11 15:22:10');
-- DATE_FORMAT()和TIME_FORMAT()功能相同
select date_format(now(),'%Y-%m-%d %H:%i:%s'); 
select time_format(now(),'%Y-%m-%d %H:%i:%s');
select date_format(now(),'%Y-%m-%d'); 
select time_format(now(),'%Y-%m-%d');