MySQL 的时间类型和时间相关的函数

这篇文章最后更新的时间在六个月之前,文章所叙述的内容可能已经失效,请谨慎参考!

类型

日期时间类型 占用空间 日期格式 最小值 最大值 零值表示 描述
DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00 年月日时分秒毫秒
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 08:00:01 2038-01-19 03:14:07 00000000000000 年月日时分秒毫秒
DATE 4 bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00 年月日
TIME 3 bytes HH:MM:SS -838:59:59 838:59:59 00:00:00 时分秒
YEAR 1 bytes YYYY 1901 2155 0000

函数

获取当前时间

select NOW(); # 当前的年月日时分秒,当前时区的
select CURDATE(); # 当前的年月日,当前时区的
select CURTIME(); # 当前的时分秒,当前时区的
select UTC_TIMESTAMP(); # 当前的年月日时分秒,utc时区的
select UTC_DATE(); # 当前的年月日,utc时区的
select UTC_TIME(); # 当前的时分秒,utc时区的
select UNIX_TIMESTAMP(); # 当前的10位时间戳

select DATE(NOW()); # 当前的年月日
select TIME(NOW()); # 当前的时分秒
select YEAR(NOW()); # 当前的年
select MONTH(NOW()); # 当前的月
select DAY(NOW()); # 当前的日
select HOUR(NOW()); # 当前的时
select MINUTE(NOW()); # 当前的分
select SECOND(NOW()); # 当前的秒

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP, 这些都是 NOW() 的别名

CURRENT_DATE(), CURRENT_DATE 这两个是 CURDATE() 的别名 CURRENT_TIME(), CURRENT_TIME 这两个是 CURTIME() 的别名

转换

在这个章节的语境下,时间戳是指 10 位长度的类型为整型的时间戳

计算

和星期相关的

函数 描述
week(date [,mode]); 一年中的第几周,礼拜日是第一天,索引从 0 开始
weekofyear(date); 一年中的第几周,索引从 1 开始,相当于 week(date, 3)
dayofweek(date); 一周中的第几天,礼拜日是第一天,索引从 1 开始
weekday(date); 一周中的第几天,礼拜一是第一天,索引从 0 开始
yearweek(date [,mode]); 返回年份和周数,例如 2022-04-21 会返回 202216 ,表示 2022 年和当年的第16周

week 和 yearweek 的 mode 是一样的。 mode 的默认值来自系统变量 default_week_format 。 可以这样查看 SHOW VARIABLES LIKE 'default_week_format'; 一般情况下 default_week_format 的值是 0 。

mode 一周的第一天 范围 第一周是怎么计算的
0 星期日 0-53 从本年的第一个星期日开始,是第一周。前面的计算为第0周
1 星期一 0-53 假如1月1日到第一个周一的天数超过3天,则计算为本年的第一周。否则为第0周
2 星期日 1-53 从本年的第一个星期日开始,是第一周。前面的计算为上年度的第5x周
3 星期一 1-53 假如1月1日到第一个周日天数超过3天,则计算为本年的第一周。否则为上年度的第5x周
4 星期日 0-53 假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为第0周
5 星期一 0-53 从本年的第一个星期一开始,是第一周。前面的计算为第0周。
6 星期日 1-53 假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为上年度的第5x周
7 星期一 1-53 从本年的第一个星期一开始,是第一周。前面的计算为上年度的第5x周

sysdate 和 now 的区别

sysdate() 日期时间函数跟 now() 类似,不同之处在于: now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。

例子

select now(), sleep(3), now(), sysdate();
# sleep 会返回 0
# 两个 now 是一样的
# sysdate 会比 now 慢 3 秒

mysql 时间的格式

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

这几个函数都通用 DATE_FORMAT(), FROM_UNIXTIME(), STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

还有更多

convert_tz
extract
timestamp
timestampadd
sec_to_time
time_to_sec
makedate
maketime
to_days
LAST_DAY
ADDTIME
sysdate
sleep

各个函数的输入和输出好像都有一点混乱

因为 mysql 的文档里函数名都是大写的,所以自己写的代码最好还是都是大写吧,虽然都是大小写不敏感。

MySQL 的时区

MySQL 的时区分为三部分,系统时区,服务器时区,会话时区。

优先级

系统时区 < 服务器时区 < 会话时区

如果会话时区会空,则会使用服务器时区,如果服务器时区为空,则会使用系统时区

查看系统时区 服务器时区 会话时区

select @@global.system_time_zone, @@global.time_zone, @@session.time_zone;

可以在 linux 的命令行里用这样的命令来查看时区

date +"%z"
# git for windows 的 bash 也支持这个命令

可以在 windows 的命令行里用这样的命令来查看时区

tzutil /g
# 或
w32tm /tz
# 或
systeminfo
# 似乎只有 win10 及之后的系统能用 tzutil /g 或 w32tm /tz

修改会话时区

set session time_zone='+08:00';

修改服务器时区

其它

时间表示格式

iso 8601

一些例子

其它位置的时区

参考