mysql中处理时间的函数
- 有关日期和时间的数据类型
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
TIME | HH:MM:SS | 838:59:59 ~ 838:59:59 | 3 个字节 |
DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8个字节 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4个字节 |
-
日期函数
函数名 作用 CURRENT_TIMESTAMP() 返回当前日期和时间 DATE() 从日期时间中提取日期部分 TIME() 从日期时间中提取时间部分 YEAR() 从日期时间中提取年份 MONTH() 从日期时间中提取月份 DAY() 从日期时间中提取日期 HOUR() 从日期时间中提取小时 MINUTE() 从日期时间中提取分钟数 SECOND() 从日期时间中提取秒数 DATE_FORMAT() 将日期时间按照指定的格式进行格式化 DATE_ADD() 在给定的日期上添加指定的时间间隔 DATE_SUB() 从给定的日期中减去指定的时间间隔 DATEDIFF() 计算两个日期之间的天数差异 TIMEDIFF() 计算两个时间之间的时间差异 TIMESTAMPDIFF() 计算两个日期时间之间的时间差异 下面是上面函数的一些案例演示,方便理解。
-
CURRENT_TIMESTAMP()函数:函数用于返回当前日期和时间。
SELECT CURRENT_TIMESTAMP(); -- 返回:2023-10-18 10:12:08
-
DATE()函数:用于从日期时间中提取日期部分。
SELECT DATE('2023-10-18 10:12:08'); -- 返回:2023-10-18
-
TIME()函数:用于从日期时间中提取时间部分。
SELECT TIME('2023-10-18 10:12:08'); -- 返回:10:12:08
-
YEAR()函数:用于从日期时间中提取年份。
SELECT YEAR('2023-10-18'); -- 返回:2023
-
MONTH()函数:用于从日期时间中提取月份。
SELECT MONTH('2023-10-18'); -- 返回:10
-
DAY()函数:用于从日期时间中提取日期。
SELECT DAY('2023-10-18'); -- 返回:18
-
HOUR()函数用于从日期时间中提取小时。
SELECT HOUR('2023-10-18 10:12:08'); -- 返回:10
-
MINUTE()函数:用于从日期时间中提取分钟数。
SELECT MINUTE('2023-10-18 10:12:08'); -- 返回:12
-
SECOND()函数:用于从日期时间中提取秒数。
SELECT SECOND('2023-10-18 10:12:08'); -- 返回:08
-
DATE_FORMAT()函数:用于将日期时间按照指定的格式进行格式化。它接受两个参数:要格式化的日期或时间值和格式字符串,并返回一个字符串。
SELECT DATE_FORMAT('2023-10-18', '%Y年%m月%d日')AS formatted_date; -- 返回:2023年10月18日
SELECT DATE_FORMAT('2022-01-01 12:34:56', '%H:%i:%s')AS formatted_date; -- 返回:12:34:56
你还可以使用其他格式控制符来定制日期和时间的格式。以下是一些常用的格式控制符:
- ‘%Y’:四位数的年份,例如 2023
- ‘%y’:两位数的年份,例如 23
- ‘%m’:两位数的月份,例如 07
- ‘%d’:两位数的日期,例如 10
- ‘%H’:24小时制的小时,例如 13
- ‘%h’:12小时制的小时,例如 01
- ‘%i’:两位数的分钟,例如 30
- ‘%s’:两位数的秒数,例如 45
- ‘%p’:AM 或 PM(仅适用于12小时制)
- ‘%W’:星期的完整名称,例如 Sunday
- ‘%w’:星期的数字表示(0 表示星期日,1 表示星期一,以此类推)
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS formatted_date; -- 返回:2023年10月18日 10:56:39 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s %p') AS formatted_date; -- 返回:2023-10-18 10:56:57 AM(当前时间为上午) SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS formatted_date; -- 返回:Wednesday, October 18, 2023(当前时间为星期三)
-
DATE_ADD()函数:用于在给定的日期上添加指定的时间间隔。它接受两个参数:要添加的日期或时间值和要添加的时间间隔。
SELECT DATE_ADD('2023-10-18', INTERVAL 7 DAY) AS new_date; --返回:2023-10-25
在 INTERVAL 关键字后面,可以使用不同的时间间隔单位来指定要添加的时间量。以下是一些常用的时间间隔单位:
- DAY:天
- HOUR:小时
- MINUTE:分钟
- SECOND:秒
- WEEK:周
- MONTH:月
- QUARTER:季度
- YEAR:年
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY) AS new_date; -- 返回当前日期和时间加上两天后的新日期和时间 SELECT DATE_ADD(NOW(), INTERVAL 3 HOUR) AS new_date; -- 返回当前日期和时间加上三个小时后的新日期和时间 SELECT DATE_ADD(NOW(), INTERVAL 4 MINUTE) AS new_date; -- 返回当前日期和时间加上四分钟后的新日期和时间 SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK) AS new_date; -- 返回当前日期和时间加上一周后的新日期和时间
-
DATE_SUB()函数用于从给定的日期中减去指定的时间间隔。它接受两个参数:要减去的日期或时间值和要减去的时间间隔。
SELECT DATE_SUB('2023-1-18', INTERVAL 1 MONTH); -- 返回:2023-09-18
DATE_SUB()和DATE_ADD()的时间间隔单位相同:
SELECT DATE_SUB(NOW(), INTERVAL 2 DAY) AS new_date; -- 返回当前日期和时间减去两天后的新日期和时间 SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR) AS new_date; -- 返回当前日期和时间减去三个小时后的新日期和时间 SELECT DATE_SUB(NOW(), INTERVAL 4 MINUTE) AS new_date; -- 返回当前日期和时间减去四分钟后的新日期和时间 SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK) AS new_date; -- 返回当前日期和时间减去一周后的新日期和时间
-
DATEDIFF()函数:用于计算两个日期之间的天数差异。它接受两个参数:要比较的日期或时间值,并返回一个整数表示两个日期之间的天数差异。
SELECT DATEDIFF('2023-10-15', '2023-10-10') AS days_diff; --返回:5,表示 2023-10-10 和 2023-10-15 之间的差异为 5 天
下面的示例,展示如何使用 DATEDIFF() 函数计算不同日期之间的差异:
SELECT DATEDIFF('2023-10-15', '2023-09-30') AS days_diff; -- 返回 36,表示 2023-06-30 和 2023-07-15 之间的差异为 15 天 SELECT DATEDIFF('2023-07-01', '2023-12-31') AS days_diff; -- 返回 154,表示 2023-12-31 和 2023-07-01 之间的差异为 154 天
-
TIMEDIFF() 函数:用于计算两个时间之间的差值。它接受两个时间参数,并返回一个时间间隔。以下是TIMEDIFF()函数的语法:
TIMEDIFF(time1, time2)
其中time1和time2是时间值,返回值是表示时间间隔的字符串,格式为:HH:MM:SS。
以下是一个示例,展示如何使用TIMEDIFF()函数计算两个时间值之间的差异:
SELECT TIMEDIFF('11:14:30', '10:00:00') AS time_diff; --返回:01:14:30
注意:TIMEDIFF函数返回的是TIME值,所以其结果被限制在从-838:59:59到838:59:59的范围内。
-
TIMESTAMPDIFF()函数:用于计算两个日期或时间之间的差异,并以指定的单位返回结果。它接受三个参数:要比较的日期或时间值、时间单位和返回类型。
SELECT TIMESTAMPDIFF(SECOND, '2023-10-18 12:00:00', '2023-09-18 13:30:00') AS time_diff; --返回:5400 表示从 '2023-07-18 12:00:00' 到 '2023-07-18 13:30:00' 之间的时间差为 5400 秒。
展示如何使用 TIMESTAMPDIFF() 函数计算不同日期之间的差异:
SELECT TIMESTAMPDIFF(WEEK, '2023-10-01', '2023-10-31') AS time_diff; -- 返回 4,表示从 '2023-10-01' 到 '2023-10-31' 之间的时间差为 4 周 SELECT TIMESTAMPDIFF(MONTH, '2023-10-01', '2023-12-31') AS time_diff; -- 返回 5,表示从 '2023-10-01' 到 '2023-12-31' 之间的时间差为 2 个月
TIMESTAMPDIFF() 函数非常灵活,因为它允许您指定要使用的单位(例如秒、分钟、小时、天、周、月等)和返回类型(例如整数表示天数、整数表示月数等)。通过使用 TIMESTAMPDIFF() 函数并提供适当的参数,您可以轻松地计算和确定两个日期或时间之间的差异,并以所需的单位返回结果。
-