Mysql日期时间函数使用大全
需求描述
需求:列出mysql常见的日期、时间函数的使用方法以及注意事项。
解决方法:通过参考官方手册并验证来完成该需求。
注:1 当前以mysql来演示。
2 详细函数使用方法及说明见SQL代码注释或者表格汇总。
SQL代码
时间加减
对日期时间类型进行指定单位的加减操作,计算后仍是时间。
-- 1 时间加减运算(对日期时间类型进行指定单位的加减操作,计算后仍是时间)
SELECT
ADDDATE('2008-01-02', INTERVAL 31 DAY) -- 日期加减,这里单位是天。
,ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') -- 日期时间加减
,DATE_ADD('1998-01-02', INTERVAL -31 DAY),DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) -- 时间加减
,DATE_SUB('1998-01-02', INTERVAL 31 DAY) -- 时间加减
,SUBDATE('2008-01-02', INTERVAL -31 DAY) -- DATE_SUB的同义词
,SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') -- 两个时间相加减
,TIMESTAMPADD(MINUTE,1,'2003-01-02') -- 时间加减
,TIMESTAMP('2003-12-31'),TIMESTAMP('2003-12-31 12:00:00','12:00:00') -- 1个参数时返回含全是0的时分秒时间;2个参数时返回两个时间的加
提取时间
-- 2 时间提取(从日期时间里提取年、季度、月(份)、周(名)、年所在周、月最后日、日、时、分、秒、微秒)
SELECT
DATE('2003-12-31 01:02:03') -- 提取日期时间里的日期
,EXTRACT(YEAR FROM '2019-07-02') -- 从日期里提取年月日时分秒
,TIME('2003-12-31 01:02:03') -- 返回日期时间里的时间
,YEAR('2021-04-09') -- 返回年
,QUARTER('2008-04-01') -- 返回日期所在的季度
,PERIOD_ADD(200801,2) -- 返日期的月份加减
,MONTH('2008-02-03') -- 返回日期的月份(数字形式)
,MONTHNAME('2008-02-03') -- 返回日期的月份名
,WEEK('2021-04-09') -- 年所在周,一般一年有53周
,WEEKDAY('2021-04-09') -- 周工作日序号,0周一, 1星期二, … 6星期天
,WEEKOFYEAR('2021-04-09') -- 返回年所在周,同week()
,YEARWEEK('2021-04-09') -- 返回年和年所在周
,DAY('2021-04-08') -- 同DAYNAME
,DAYNAME('2021-04-08') -- 周所在星期数,与lc_time_names有关,中文显示"星期四"
,DAYOFMONTH('2021-04-08') -- 月里对应的日期
,DAYOFWEEK('2021-04-08') -- 1 是星期天 2 是星期1依次类推
,DAYOFYEAR('2021-04-08') -- 日期所在年的天数
,HOUR('10:05:03') -- 提取时间里的小时
,LAST_DAY('2021-02-05')-- 返回日期所在月的最后一天
,MINUTE('2008-02-03 10:05:03') -- 返回时间的分钟
,SECOND('10:05:03') -- 返回秒数
,MICROSECOND('12:00:00.123456')-- 返回时间的微秒
时间格式化
-- 3 时间格式化(对日期时间、字符串类型按照指定的日期或时间格式进行转换)
SELECT
DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'USA')) -- 返回一个格式化的日期字符串
,DATE_FORMAT('2009-10-04 22:23:00', '%Y %m %d') -- 时间格式化
,STR_TO_DATE('01,5,2013','%d,%m,%Y') -- 字符串按指定格式转为时间
,TIME_FORMAT('100:00:00', '%H %k %h %I %l') -- 时间格式化,时间大于23时,%h %I %l返回除12后的模
生成时间间隔
比较两个时间,生成时间指定单位的时间差,结果是整型数。
-- 4 生成时间间隔(比较两个时间,生成时间指定单位的时间差,结果是整型数)
SELECT
TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') -- 指定单位的时间差
,UNIX_TIMESTAMP('1970-01-01 10:00:02')-- 返回Unix时间差,计算方法:(10-8)*3600+2 = 7202 比较时间时以UTC时间为基准,如果当前时间是东8区则要减去8换算成UTC时间
,TIME_TO_SEC('00:39:38') -- 将时间转换为对应的秒数,与SEC_TO_TIME()对应
,TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') -- 时间差
,DATEDIFF('2007-12-31 23:59:59','2007-12-29') -- 两个时间差
,PERIOD_DIFF(200802,200703) -- 返回两个日期的月份差
,TO_DAYS(950501) -- 距离0年的天数,这里是19950501
,TO_SECONDS(950501),TO_SECONDS('19950501'),TO_SECONDS(0101),TO_SECONDS('20000101') -- 距离0年的秒数
时间间隔生成时间
通过时间间隔转换为时间,对比的基准是0年,特别的UNIXTIME的是1970年1月1号 0:00:00,这里是UTC时间。
-- 5 时间间隔生成时间(通过时间间隔转换为时间,对比的基准是0年,特别的UNIXTIME的是1970年1月1号 0:00:00,这里是UTC时间)
SELECT
FROM_DAYS(730669) -- 把天数为日期
,FROM_UNIXTIME(1447430881) -- 返回距离1970-01-01的时间
,SEC_TO_TIME(2378) -- 将一个数(秒为单位)转换为时间的hh:mm:ss的格式
系统时间
-- 6 当前系统时间(返回当前系统的时间)
SELECT
CURDATE(),CURDATE() + 0 -- 返回YYYY-MM-DD或者YYYYMMDD格式的日期时间
,CURRENT_DATE(),CURRENT_DATE -- CURDATE的同义词,但返回的是时间格式,时分秒为0
,CURTIME(),CURTIME(4) + 0 -- 仅返回时间(时分秒),参数为时间的精度,小数点后位数。
,CURRENT_TIMESTAMP -- 同now()
,LOCALTIME -- 同now()
,LOCALTIMESTAMP -- 同now()
,NOW() -- 获取当前系统时间
,SYSDATE() -- 获取当前系统时间
生成时间
根据给定的整型的年、月、日生成日期;时、分、秒生成时间。
-- 7 生成时间(根据给定的整型的年、月、日生成日期;时、分、秒生成时间)
SELECT
MAKEDATE(2011,32) -- 生成个日期
,MAKETIME(12,15,30)-- 生成个时间
时区相关
时区转换以及UTC时间。
-- 8 时区相关(时区转换以及UTC时间)
SELECT
CONVERT_TZ('2021-04-08 12:00:00','-08:00','+10:00')
,UTC_DATE() -- 返回当前UTC日期
,UTC_TIME() -- 返回当前UTC时间
,UTC_TIMESTAMP() -- 返回当前UTC时间和日期
完整汇总
-- mysql
SELECT
ADDDATE('2008-01-02', INTERVAL 31 DAY) -- 日期加减
,ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') -- 日期时间加减
,CONVERT_TZ('2021-04-08 12:00:00','-08:00','+10:00')
,CURDATE(),CURDATE() + 0 -- 返回YYYY-MM-DD或者YYYYMMDD格式的日期时间
,CURRENT_DATE(),CURRENT_DATE -- CURDATE的同义词,但返回的是时间格式,时分秒为0
,CURRENT_TIME(),CURRENT_TIME -- CURTIME()的同义词
,CURTIME(),CURTIME(4) + 0 -- 仅返回时间(时分秒),参数为时间的精度,小数点后位数。
,CURRENT_TIMESTAMP -- 同now()
,DATE('2003-12-31 01:02:03') -- 提取日期时间里的日期
,DATEDIFF('2007-12-31 23:59:59','2007-12-29') -- 两个时间差
,DATE_ADD('1998-01-02', INTERVAL -31 DAY),DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) -- 时间加减
,DATE_FORMAT('2009-10-04 22:23:00', '%Y %m %d') -- 时间格式化
,DATE_SUB('1998-01-02', INTERVAL 31 DAY) -- 时间加减
,DAY('2021-04-08') -- 同DAYNAME
,DAYNAME('2021-04-08') -- 周所在星期数,与lc_time_names有关,中文显示"星期四"
,DAYOFMONTH('2021-04-08')
,DAYOFWEEK('2021-04-08') -- 1 是星期天 2 是星期1依次类推
,DAYOFYEAR('2021-04-08') -- 日期所在年的天数
,EXTRACT(YEAR FROM '2019-07-02') -- 从日期里提取年月日时分秒
,FROM_DAYS(730669) -- 把天数为日期
,FROM_UNIXTIME(1447430881) -- 返回距离1970-01-01的时间
,DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'USA')) -- 返回一个格式化的日期字符串
,HOUR('10:05:03') -- 提取时间里的小时
,LAST_DAY('2021-02-05')-- 返回日期所在月的最后一天
,LOCALTIME -- 同now()
,LOCALTIMESTAMP -- 同now()
,MAKEDATE(2011,32) -- 生成个日期
,MAKETIME(12,15,30)-- 生成个时间
,MICROSECOND('12:00:00.123456')-- 返回时间的微秒
,MINUTE('2008-02-03 10:05:03') -- 返回时间的分钟
,MONTH('2008-02-03') -- 返回日期的月份(数字形式)
,MONTHNAME('2008-02-03') -- 返回日期的月份名
,NOW() -- 获取当前系统时间
,PERIOD_ADD(200801,2) -- 返日期的月份加减
,PERIOD_DIFF(200802,200703) -- 返回两个日期的月份差
,QUARTER('2008-04-01') -- 返回日期所在的季度
,SEC_TO_TIME(2378) -- 将一个数(秒为单位)转换为时间的hh:mm:ss的格式
,SECOND('10:05:03') -- 返回秒数
,STR_TO_DATE('01,5,2013','%d,%m,%Y') -- 字符串按指定格式转为时间
,SUBDATE('2008-01-02', INTERVAL -31 DAY) -- DATE_SUB的同义词
,SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') -- 两个时间相加减
,SYSDATE() -- 获取当前系统时间
,TIME('2003-12-31 01:02:03') -- 返回日期时间里的时间
,TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') -- 时间差
, TIME_FORMAT('100:00:00', '%H %k %h %I %l') -- 时间格式化,时间大于23时,%h %I %l返回除12后的模
,TIME_TO_SEC('00:39:38') -- 将时间转换为对应的秒数,与SEC_TO_TIME()对应
,TIMESTAMP('2003-12-31'),TIMESTAMP('2003-12-31 12:00:00','12:00:00') -- 1个参数时返回含全是0的时分秒时间;2个参数时返回两个时间的加
,TIMESTAMPADD(MINUTE,1,'2003-01-02') -- 时间加减
,TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') -- 指定单位的时间差
,TO_DAYS(950501) -- 距离0年的天数
,TO_SECONDS(950501),TO_SECONDS('19950501'),TO_SECONDS(0101),TO_SECONDS('20000101') -- 距离0年的秒数
,UNIX_TIMESTAMP('1970-01-01 10:00:02')-- 返回Unix时间差,计算方法:(10-8)*3600+2 = 7202 比较时间时以UTC时间为基准,如果当前时间是东8区则要减去8换算成UTC时间
,UTC_DATE() -- 返回当前UTC日期
,UTC_TIME() -- 返回当前UTC时间
,UTC_TIMESTAMP() -- 返回当前UTC时间和日期
,WEEK('2021-04-09') -- 年所在周,一般一年有53周
,WEEKDAY('2021-04-09') -- 周工作日序号,0周一, 1星期二, … 6星期天
,WEEKOFYEAR('2021-04-09')
,YEAR('2021-04-09') -- 返回年
,YEARWEEK('2021-04-09') -- 返回年和年所在周
执行结果
注:仅截取前面部分结果, 完整版见文末附图。
函数使用详解
mysql日期、时间函数功能与使用方法详解。该函数按照字母顺序整理。
序号 | 函数名 | 功能 | 参数说明 |
1 | ADDDATE() | 日期加减函数 | 参数是时间、间隔 |
2 | ADDTIME() | 时间加减函数 | 参数是时间、间隔 |
3 | CONVERT_TZ() | 时区转换 | 参数是时间、原时区、新时区 |
4 | CURDATE() | 当前日期 | 1 默认返回形式是字符串:hh:mm:ss 2 CURDATE()+0的返回形式为:hhmmss |
5 | CURRENT_DATE(), CURRENT_DATE | CURDATE()的同义词 | 同CURDATE() |
6 | CURRENT_TIME(), CURRENT_TIME | CURTIME()的同义词 | 同CURTIME() |
7 | CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | NOW()的同义词 | 同now() |
8 | CURTIME() | 当前时间 | 1 默认返回形式是字符串:hh:mm:ss 2 CURTIME()+0的返回形式为:hhmmss 3 参数fsp是指定秒的精度0到6,即小数点位数 |
9 | DATE() | 从日期、时间类型里提取日期 | 时间、日期 |
10 | DATE_ADD() | 日期加减函数 | 参数是时间(日期)、间隔、单位 |
11 | DATE_FORMAT() | 时间格式转换 | 参数是时间(日期)、格式 |
12 | DATE_SUB() | 日期加减函数 | 参数是时间(日期)、间隔、单位 |
13 | DATEDIFF() | 两个时间的差 | 参数是两个时间(日期) |
14 | DAY() | DAYOFMONTH()的同义词 | 参数是时间(日期) |
15 | DAYNAME() | 周所在星期数 | 1 参数是时间(日期) 2 周所在星期数,与lc_time_names有关,中文显示"星期四" 3 需要设置SET lc_time_names = 'zh_CN'或者全局设置 |
16 | DAYOFMONTH() | 月里对应的日期 | 参数是时间(日期) |
17 | DAYOFWEEK() | 周所在星期数(数字形式) | 参数是时间(日期) |
18 | DAYOFYEAR() | 日期所在年的天数 | 参数是时间(日期) |
19 | EXTRACT() | 提取日期、时间类型的部分信息 | 参数是时间(日期)、单位 |
20 | FROM_DAYS() | 把天数为日期 | 1 参数是个整型数 2 以0年为参考起点 |
21 | FROM_UNIXTIME() | 格式化Unix时间戳为日期 | 1 参数是个整型数 2 以1970-01-01为参考起点 3 参数可指定时间格式 4 时间范围[1970-01-01 00:00:00.000000,2038-01-19 3:14:07.999999] 5 这里的时间是UTC格式 |
22 | GET_FORMAT() | 返回一个格式化的日期字符串 | 1 格式支持EUR、USA、JIS、ISO、INTERNAL 2 一般结合DATE_FORMAT()一切使用 |
23 | HOUR() | 提取时间里的小时 | 参数是时间(日期) |
24 | LAST_DAY | 返回日期所在月的最后一天 | 参数是时间(日期) |
25 | LOCALTIME(), LOCALTIME | NOW()的同义词 | 参数是时间(日期) |
26 | LOCALTIMESTAMP, LOCALTIMESTAMP() | NOW()的同义词 | 参数是时间(日期) |
27 | MAKEDATE() | 通过年和日创建一个日期 | 参数年整型数年、年所在日,其中日需大于0 |
28 | MAKETIME() | 通过时分秒创建一个时间 | 参数年整型时、分、秒 |
29 | MICROSECOND() | 返回时间的微秒 | 1 参数是时间(日期) 2 微秒是百万分之一秒 |
30 | MINUTE() | 返回时间的分钟 | 参数是时间(日期) |
31 | MONTH() | 返回日期的月份 | 参数是时间(日期) |
32 | MONTHNAME() | 返回月份的名称 | 1 参数是时间(日期) 如:SET lc_time_names = 'zh_CN' |
33 | NOW() | 返回当前时间 | 参数fsp即秒的精度,0-6,即小数点后0到6位 |
34 | PERIOD_ADD() | 返日期的月份加减 | 1 参数是整型类型日期 2 格式YYMM、YYYYMM |
35 | PERIOD_DIFF() | 返回两个日期的月份差 | 1 参数是整型类型日期 2 格式YYMM、YYYYMM |
36 | QUARTER() | 返回日期所在的季度 | 1 参数是时间(日期) 2 返回1-4之间的一个数 |
37 | SEC_TO_TIME() | 将一个数(秒)转换为时间的hh:mm:ss的格式 | 1 参数是一个整型数 2 取值范围是[-838:59:59,838:59:59]与TIME类型的范围一致。 |
38 | SECOND() | 返回秒数 | 参数是时间(日期) |
39 | STR_TO_DATE() | 将字符串转换为日期 | 参数字符串、时间格式 |
40 | SUBDATE() | DATE_SUB的同义词 | 参数是时间(日期)、间隔 |
41 | SUBTIME() | 提取时间 | 参数是两个时间(日期) |
42 | SYSDATE() | 返回系统时间 | 参数fsp即秒的精度,0-6,即小数点后0到6位 |
43 | TIME() | 返回时间类型的时分秒 | 参数是时间(日期) |
44 | TIME_FORMAT() | 时间格式化 | 1 参数是时间(日期)、格式 2 参数仅为时、分、秒 3 时间大于23时,%h %I %l返回除12后的模 |
45 | TIME_TO_SEC() | 将时间转换为对应的秒数 | 1 参数是时间 2 与SEC_TO_TIME()对应 |
46 | TIMEDIFF() | 时间类型的时间差 | 参数是两个时间(日期) |
47 | TIMESTAMP() | 返回时间戳 | 1个参数时返回含全是0的时分秒时间 2个参数时返回两个时间的加 |
48 | TIMESTAMPADD() | 时间戳类型的加减 | 参数是时间(日期)、间隔、单位 |
49 | TIMESTAMPDIFF() | 时间戳类型的时间差 | 参数是时间(日期)、间隔、单位 |
50 | TO_DAYS() | 返回以天为单位距离0年的时间差 | 参数是整型数 |
51 | TO_SECONDS() | 返回以秒为单位距离0年的时间差 | 1 参数是时间(日期) 2 可以传整型数,会自动转为时间类型,比如950501即为1995-05-01 |
52 | UNIX_TIMESTAMP() | 返回Unix时间差 | 1 参数是整型数 2 基准日期是1970-01-01 00:00:00.000000 3 比较时间时以UTC时间为基准,如果当前时间是东8区则要减去8换算成UTC时间 4 合理的时间范围: [0,2147483647.999999] |
53 | UTC_DATE() | 返回当前UTC日期 | 1 默认返回形式是字符串:YYY-MM-DD 2 UTC_DATE()+0的返回形式为:YYYMMDD |
54 | UTC_TIME() | 返回当前UTC时间 | 1 默认返回形式是字符串:hh:mm:ss 2 UTC_DATE()+0的返回形式为:hhmmss |
55 | UTC_TIMESTAMP() | 返回当前UCT日期和时间 | 1 参数fsp即秒的精度,0-6,即小数点后0到6位 2 默认返回形式是字符串:hh:mm:ss 3 UTC_DATE()+0的返回形式为:hhmmss |
56 | WEEK() | 返回年所在周 | 参数是时间(日期)、年对应周类型 |
57 | WEEKDAY() | 返回周工作日序号 | 1 参数是时间(日期) 2 0周一, 1星期二, … 6星期天 |
58 | WEEKOFYEAR() | 返回年所在周 | 1 参数是时间(日期) 2 同week() |
59 | YEAR() | 返回日期里的年份 | 参数是时间(日期) |
60 | YEARWEEK() | 返回年和年所在周 | 参数是时间(日期) |
图片版
时区乱码
如果出现如下错误:
The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone
或者直接在mysql客户端里查看时区时
-- 查看时区相关参数的配置
show variables like '%time_zone%';
解决方法:需要设置数据库时区或者修改JDBC的URL。以下以北京时间(即东八区为例)
- 在mysql里通过命令行或者在配置文件里修改
-- Way1 mysql命令行修改时区
set global time_zone='+8:00'
/*
Way2 mysql配置文件里的mysqld栏位下输入如下参数保存,重启数据库服务
default-time-zone = '+8:00’
*/
- 在mysql的JDBC URL里追加serverTimezone=GMT%2B8 ,类似如下:
jdbc:mysql://127.0.0.1:3306/testDB?serverTimezone=GMT%2B8