1. mysql中日期数据类型
mysql8.0 中共有5中日期数据类型,常用的是DATE
、DATETIME
、TIMESTAMP
类型 | 大小(bytes) | 范围 | 格式 | 描述 |
---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 | 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS |
2. 日期函数
2.1 获取当前时间相关
函数 | 描述 | 示例 | 返回值 |
---|
NOW() | 返回当前日期和时间 | SELECT NOW(); | 2023-03-25 21:29:35 |
CURTIME() | 返回当前时间 | SELECT CURTIME(); | 21:31:20 |
CURRENT_TIME() | 返回当前时间 | SELECT CURRENT_TIME(); | 21:31:20 |
CURRENT_TIMESTAMP() | 返回当前日期和时间 | SELECTCURRENT_TIMESTAMP(); | 2023-03-25 21:33:10 |
UNIX_TIMESTAMP() | 返回从1970-01-01 00:00:00到当前毫秒值 | select UNIX_TIMESTAMP(); | 1679751341 |
CURDATE() | 返回当前日期 | SELECT CURDATE(); | 2023-03-25 |
CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE(); | 2023-03-25 |
2.2 日期抽取相关函数
函数 | 描述 | 示例 | 返回值 |
---|
EXTRACT(type FROM d) | 从日期 d 中获取指定的值,type 指定返回的值。type可取值为:MICROSECOND、SECOND、MINUTE HOUR 等 | SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:12:11’) | 12 |
YEAR() | 返回年份 | SELECT YEAR(“2017-06-15”); | 2017 |
MONTH(d) | 返回月份 | SELECT MONTH(‘2011-11-11 11:11:11’); | 11 |
DAY(d) | 返回日期 | SELECT DAY(“2017-06-15”); | 15 |
HOUR(d) | 返回小时 | SELECT HOUR(‘2011-11-12 13:14:15’); | 13 |
MINUTE(d) | 返回分钟 | SELECT MINUTE(‘2011-11-12 13:14:15’); | 14 |
SECOND(d) | 返回秒 | SELECT SECOND(‘2011-11-12 13:14:15’); | 15 |
QUARTER(d) | 返回季度 | SELECT QUARTER(‘2011-11-12 13:14:15’); | 4 |
MONTHNAME(d) | 返回月份名称 | SELECT MONTHNAME(‘2011-11-12 13:14:15’); | November |
DAYNAME(d) | 返回d是星期几 | SELECT DAYNAME(‘2011-11-12 13:14:15’); | Saturday |
DAYOFMONTH(d) | 返回d是本月的第几天 | SELECT DAYNAME(‘2011-11-12 13:14:15’); | 12 |
LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY(‘2011-11-12 13:14:15’); | 2011-11-30 |
DAYOFWEEK(d) | 返回d是星期几,1是星期日,2是星期1… | SELECT DAYOFWEEK(‘2011-11-12 13:14:15’); | 7 |
DAYOFYEAR(d) | 返回d是当年的第几天 | SELECT DAYOFYEAR(‘2011-11-12 13:14:15’); | 316 |
WEEK(d) | 计算日期 d 是本年的第几个星期,范围是 0 到 53 | SELECT WEEK(‘2011-11-12 13:14:15’); | 15 |
WEEKDAY(d) | 日期 d 是星期几,0 表示星期一,1 表示星期二 | SELECT WEEKDAY(‘2011-11-12 13:14:15’); | 5 |
WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期,范围是 0 到 53 | SELECT WEEKOFYEAR(‘2011-11-12 13:14:15’); | 45 |
YEARWEEK(d) | 返回年份及第几周(0到53),mode 中 0 表示周天,1表示周一,以此类推 | SELECT YEARWEEK(‘2011-11-12 13:14:15’); | 201145 |
2.3 日期运算相关
函数 | 描述 | 示例 | 返回值 |
---|
DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) | 32 |
TIMEDIFF(time1, time2) | 计算时间差 | SELECT TIMEDIFF(“13:10:11”, “13:10:10”); | 00:00:01 |
DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 | SELECT DATE_SUB(‘2011-11-12 13:14:15’,INTERVAL 2 DAY) | 2011-11-10 13:14:15 |
计算起始日期d加上一个时间段后的日期
ADDDATE/DATE_ADD(d,INTERVAL expr type)
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
其他相关日期计算函数使用方式均一致
2.4 时区相关函数
函数 | 描述 | 返回值 |
---|
UTC_DATE() | 返回UTC时区的日期 | 2023-03-25 |
UTC_TIME() | 返回UTC时区的时间 | 14:41:10 |
UTC_TIMESTAMP() | 返回UTC时区的日期和时间 | 2023-03-25 14:41:10 |
CONVERT_TZ() | 从一个时区转到另一个时区 | 2023-03-25 22:41:10 |
2.5 日期格式化相关
2.5.1 将毫秒值时间戳转为指定格式日期
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])
SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s');
2.5.2 按照指定的表达式显示日期
DATE_FORMAT(d,f)
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
2.5.3 将字符串转变为日期
STR_TO_DATE(string, format_mask)
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
3. 常见日期格式
format参数
格式如下:
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位