时间是一类重要的数据,MySQL中有多种关于时间的类型可以选择。这篇文章主要介绍MySQL中的时间类型,主要参考MySQL文档:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
1. 时间类型
MySQL中的时间类型有三大类:日期(Date)、时间(Time)和年(Year)。
1.1 基本信息
下面的图表展示了MySQL几种类型的基本信息:
类型 | 格式 | 范围 | 零值 | 空间大小(MySQL 8.0) |
---|---|---|---|---|
YEAR | ‘YYYY’ | 1901 to 2155 | 0000 | 1字节 |
DATE | ‘YYYY-MM-DD’ | '1000-01-01' to '9999-12-31' | ‘0000-00-00’ | 3字节 |
TIME | ‘hh:mm:ss’ | '-838:59:59' to '838:59:59' | ‘00:00:00’ | 3字节 |
TIMESTAMP | ‘YYYY-MM-DD hh:mm:ss’ | '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC | ‘0000-00-00 00:00:00’ | 4字节 |
DATETIME | ‘YYYY-MM-DD hh:mm:ss’ | '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' | ‘0000-00-00 00:00:00’ | 5字节 |
关于日期与时间类型,需要关注:
- 支持时间的类型有:
TIME
、DATETIME
和TIMESTAMP
; - 支持日期的类型有:
DATE
、DATETIME
和TIMESTAMP
; - 支持小数秒的类型有:
TIME
、DATETIME
和TIMESTAMP
; - 特殊的类型:
YEAR
; - MySQL 8.0不支持两位的
YEAR
类型; - 小数秒的精度可选值是0-6,默认是0,3代表毫秒,6代表微秒,而2代表10毫秒(如0.11就是110毫秒);
- 存储
TIMESTAMP
值时会将时间从当前时区转换成UTC时间,返回时再转换回当前时区; - 默认情况下连接的时区就是服务器的时区,当然每个连接也可以设置自己的时区;
TIME
类型还可以用来表示时间间隔;- 合法但是超过范围的
TIME
值会保存为最近的边界值,比如-850:00:00保存为-838:59:59; TIMESTAMP
有2038问题;TIMESTAMP
和DATETIME
都可以设置自动插入时间与更新时间;- 使用频率最高的是
DATETIME
和TIMESTAMP
。
1.2 自动更新
TIMESTAMP
和DATETIME
可以设置自动初始化与更新:
CREATE TABLE t1 (
id int,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
这样,如果新增记录时没有指定ts
和dt
的值,那它们默认就是当前时间。
更新记录的时候,也会更新为当前时间。
一般来说我们会创建两个时间字段,一个用于记录创建时间,一个用于记录更新时间:
CREATE TABLE t1 (
id int,
ctime DATETIME DEFAULT CURRENT_TIMESTAMP,
utime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
这两个字段使用的频率很高。
1.3 零值与NULL
五种时间相关的类型都有各自的零值,但是否允许零值有些不同。
默认情况下MySQL不允许日期中有零值,比如2020-00-01等,这通过NO_ZERO_IN_DATE
和NO_ZERO_DATE
两个SQL模式控制的(这两个模式都已废弃)。
可以通过下面的语句查看当前的SQL模式(session.sql_mode
就是当前连接的SQL模式):
mysql> select @@global.sql_mode\G
*************************** 1. row ***************************
@@global.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.04 sec)
SQL模式控制了MySQL的不同行为,就像是配置文件一样,详细介绍可以参考https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
默认情况下DATE
, DATETIME
不允许有零值,甚至不允许月份或日期有零值,即0000-00-01和0000-01-00都是不允许的。
而TIME
和YEAR
可以有零值。
关于NULL
,复杂的是TIMESTAMP
。对于TIME
,DATE
,DATETIME
和YEAR
都是允许NULL
值的。
只有TIMESTAMP
受变量explicit_defaults_for_timestamp
的影响。
变量explicit_defaults_for_timestamp
允许服务器对TIMESTAMP
的默认值与NULL
值的非标准行为,这些行为会产生一些莫名奇怪的行为,因此在8.0.18版本中,这个变量已经弃用。
在5.7版本,这个变量默认关闭,而在8.0中是开启的。
可以查看这个变量的值:
select @@explicit_defaults_for_timestamp;
0就是关闭,1就是开启。
更多关于变量
explicit_defaults_for_timestamp
的介绍,可以参考https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
在关闭状态下,TIMESTAMP
会有一些奇怪的行为:
-
不允许有
NULL
默认值:创建表时如果一个TIMESTAMP
字段默认值NULL
会报错:mysql> create table t_null (ts timestamp default null); ERROR 1067 (42000): Invalid default value for 'ts'
-
如果没有显式设置
NULL
,默认值或ON UPDATE
属性,那么第一个TIMESTAMP
字段会有自动初始化与自动更新属性:mysql> create table ts (ts timestamp); mysql> desc ts; +-------+-----------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------------------------+ | ts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------------------------+
而在开启时,TIMESTAMP
就显得正常多了:
- 允许有
NULL
属性,这样可以插入NULL
值; - 不会自动添加
DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
属性。
所以在使用TIMESTAMP
时,一定要注意这些容易忽略的地方。
1.4 时区
时区的设置会影响到TIMESTAMP
:
- 当MySQL服务器启动的时候,会查找系统环境来决定时区;
- 可以使用
--default-time-zone
来指定时区; - 客户端连接时,默认使用服务器的时区,当客户端与服务器处于同一个时区的时候没什么问题,但是当时区不同时,应该显式设置连接的时区;
- 客户端传递给服务器的
TIMESTAMP
值,服务器会先转换成UTC时间戳存储;当客户端查询时,再转换成客户端时区的值; - 其它时间相关的类型没有时区信息。
查看当前时区:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
SYSTEM
意味着服务器的时区和系统保持一致。
假如SYSTEM
是东八区,一个处于同时区的客户端C1已经存储了下面的数据:
mysql> create table ts (ts timestamp);
mysql> insert into ts values (now());
mysql> select * from ts;
+---------------------+
| ts |
+---------------------+
| 2022-08-30 14:13:02 |
+---------------------+
那么另一个处于东四区的客户端C2连接后它的时区仍然是SYSTEM
东八区,这样这个客户端查询存储的时间是有问题的:
mysql> select * from ts;
+---------------------+
| ts |
+---------------------+
| 2022-08-30 14:13:02 |
+---------------------+
C2应该设置自己的时区来显示相应的时间:
mysql> set session time_zone = '+4:00';
mysql> select * from ts;
+---------------------+
| ts |
+---------------------+
| 2022-08-30 10:13:02 |
+---------------------+
1.5 内部表示
下表展示了五种时间类型所需存储大小:
类型 | MySQL 5.6.4之前 | MySQL 5.6.4及之后 |
---|---|---|
YEAR | 1字节,小端序 | 1字节,小端序 |
DATE | 3字节,小端序 | 3字节,小端序 |
TIME | 3字节,小端序 | 3字节+小数秒存储,大端序 |
TIMESTAMP | 4字节,小端序 | 4字节+小数秒存储,大端序 |
DATETIME | 8字节,小端序 | 5字节+小数秒存储,大端序 |
在5.6.4版本之前:
YEAR
:使用一个字节数字来存储。一个字节的数字范围是0到255,0表示0000,时间从1901到2155刚好255个值;DATE
:三个字节的数字,这个数字由YYYY*16*32 + MM*32 + DD
得到。比如2022-08-30,那么就会得到1035550,反过来也可以得到对应的年月日:year = n/(16*32)
,month = (n/32)%16
,day = n % 32
;这种形式比年月日分别一个字节存储扩大了范围,毕竟月最大12而日最大31,使用一个字节存储有点浪费;TIME
:也是三个字节的数字,这个数字由DD*24*3600 + HH*3600 + MM*60 + SS
得到。比如25:10:20,得到90620,反过来也可以得到对应的时分秒:h = n/3600
,m = (n/60)%60
,s = n%60
;TIMESTAMP
:四个字节的数字,用于储存时间戳,这个就比较简单了;DATETIME
:八个字节的数字,四个字节的数字用于表示日期:YYYY*10000 + MM*100 + DD
,四个字节的数字用于表示时间:HH*10000 + MM*100 + SS
。同样,可以通过数字反向得到对应的具体时间。
添加小数秒的支持后,支持小数秒的TIME
,TIMESTAMP
和DATETIME
存储发生了变化,使用大端序进行储存,跟着可选的小数秒,同时DATETIME
也进行了优化。而YEAR
和DATE
没有变化。
-
TIME
无小数秒部分:1 bit sign (1= non-negative, 0= negative) 1 bit unused (reserved for future extensions) 10 bits hour (0-838) 6 bits minute (0-59) 6 bits second (0-59) --------------------- 24 bits = 3 bytes
-
DATETIME
无小数秒部分:1 bit sign (1= non-negative, 0= negative) 17 bits year*13+month (year 0-9999, month 0-12) 5 bits day (0-31) 5 bits hour (0-23) 6 bits minute (0-59) 6 bits second (0-59) --------------------------- 40 bits = 5 bytes
sign一直是1,值0保留。
小数秒部分存储如下:
fsp | 存储 |
---|---|
0 | 0字节 |
1, 2 | 1字节 |
3, 4 | 2字节 |
5, 6 | 3字节 |
2. 常见用法
了解了五种类型的基本信息之后,这部分重点介绍一下MySQL中关于时间类型的一些常见用法。
这些常见用法都是MySQL的函数,大多数在我们的程序中是用不到的,但对于平时小型的统计任务还是很有帮助的。
2.1 获取当前时间
MySQL支持一些函数来获取当前时间:
CURDATE()
,CURTIME()
,NOW()
:可以获取客户端所在时区的当前时间;UTC_DATE()
,UTC_TIME()
,UTC_TIMESTAMP()
:可以获得当前的UTC时间;CURRENT_DATE()
,CURRENT_TIME()
,CURRENT_TIMESTAMP()
:就是CURDATE()
,CURTIME()
,NOW()
的别名。
mysql> SELECT CURDATE(), CURTIME(), NOW();
+------------+-----------+---------------------+
| CURDATE() | CURTIME() | NOW() |
+------------+-----------+---------------------+
| 2022-08-30 | 11:45:47 | 2022-08-30 11:45:47 |
+------------+-----------+---------------------+
mysql> SELECT UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP();
+------------+------------+---------------------+
| UTC_DATE() | UTC_TIME() | UTC_TIMESTAMP() |
+------------+------------+---------------------+
| 2022-08-30 | 07:46:10 | 2022-08-30 07:46:10 |
+------------+------------+---------------------+
mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2022-08-30 | 11:46:31 | 2022-08-30 11:46:31 |
+----------------+----------------+---------------------+
2.2 时间的解析
MySQL支持获取时间的某一部分,比如年、月、日、时、分或秒等:
函数 | 返回值 |
---|---|
YEAR() | 日期的年份 |
MONTH() | 月份的数字,从1到12 |
MONTHNAME() | 月份名字,January到December |
DAYOFMONTH() | 日期的数字,从1到31 |
DAYNAME() | 星期的名字,Sunday到Saturday |
DAYOFWEEK() | 星期的数字,周日开始,范围是1到7 |
WEEKDAY() | 星期的数字,周一开始,范围是0到6 |
DAYOFYEAR() | 日期在一整年中的数字,1到366 |
HOUR() | 小时数,0到23 |
MINUTE() | 分钟数,0到59 |
SECOND() | 秒数,0到59 |
MICROSECOND() | 微秒数,0到999999 |
2.3 构建时间
反过来,我们也可以通过指定日期与时间的部分值,来构建一个时间:
MAKETIME
将时分秒三个数字构建成一个时间;DATE_FORMAT
和TIME_FORMAT
函数可以替换给定时间的某些部分;- 使用函数
CONCAT
可以将DATE_FORMAT
和TIME_FORMAT
的结果拼接起来。
使用MAKETIME
构建时间:
mysql> SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11), MAKETIME(999,90,90);
+--------------------+-------------------+---------------------+
| MAKETIME(10,30,58) | MAKETIME(-5,0,11) | MAKETIME(999,90,90) |
+--------------------+-------------------+---------------------+
| 10:30:58 | -05:00:11 | NULL |
+--------------------+-------------------+---------------------+
使用DATE_FORMAT
进行部分替换:
mysql> SELECT d, DATE_FORMAT(d,'%Y-%m-01') AS d1, DATE_FORMAT(d, '%Y-01-01') AS d2 FROM date_val;
+------------+------------+------------+
| d | d1 | d2 |
+------------+------------+------------+
| 1864-02-28 | 1864-02-01 | 1864-01-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 | 1999-01-01 |
| 2000-06-04 | 2000-06-01 | 2000-01-01 |
| 2017-03-16 | 2017-03-01 | 2017-01-01 |
+------------+------------+------------+
TIME_FORMAT
进行部分替换:
mysql> SELECT t1, TIME_FORMAT(t1,'%H:%i:00') AS t2, TIME_FORMAT(t1,'%H:01:02') AS t3 FROM time_val;
+----------+----------+----------+
| t1 | t2 | t3 |
+----------+----------+----------+
| 15:00:00 | 15:00:00 | 15:01:02 |
| 05:01:30 | 05:01:00 | 05:01:02 |
| 12:30:20 | 12:30:00 | 12:01:02 |
+----------+----------+----------+
DATE_FORMAT
和TIME_FORMAT
的拼接:
mysql> SELECT dt, CONCAT(DATE_FORMAT(dt, '%Y-%m-01'), ' ',
-> TIME_FORMAT(dt, '%H:00:00')) AS dt1 FROM datetime_val;
+---------------------+---------------------+
| dt | dt1 |
+---------------------+---------------------+
| 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
| 1999-12-31 09:00:00 | 1999-12-01 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-01 15:00:00 |
| 2017-03-16 12:30:15 | 2017-03-01 12:00:00 |
+---------------------+---------------------+
2.4 时间与基本单位的转换
我们可以将时间、日期与秒、天等互相转换。
TIME_TO_SEC()
与SEC_TO_TIME()
:
mysql> SELECT @t, TIME_TO_SEC(@t), SEC_TO_TIME(TIME_TO_SEC(@t));
+----------+-----------------+------------------------------+
| @t | TIME_TO_SEC(@t) | SEC_TO_TIME(TIME_TO_SEC(@t)) |
+----------+-----------------+------------------------------+
| 01:25:10 | 5110 | 01:25:10 |
+----------+-----------------+------------------------------+
FROM_DAYS()
与TO_DAYS()
:
mysql> SELECT d, TO_DAYS(d) AS 'DATE to days',
-> FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE' FROM date_val;
+------------+--------------+----------------------+
| d | DATE to days | DATE to days to DATE |
+------------+--------------+----------------------+
| 1864-02-28 | 680870 | 1864-02-28 |
| 1900-01-15 | 693975 | 1900-01-15 |
| 1999-12-31 | 730484 | 1999-12-31 |
| 2000-06-04 | 730640 | 2000-06-04 |
| 2017-03-16 | 736769 | 2017-03-16 |
+------------+--------------+----------------------+
FROM_UNIX
与UNIX_TIMESTAMP
:
mysql> SELECT dt,
-> UNIX_TIMESTAMP(dt) AS seconds,
-> FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp
-> FROM datetime_val;
+---------------------+------------+---------------------+
| dt | seconds | timestamp |
+---------------------+------------+---------------------+
| 1970-01-01 00:00:00 | 0 | 1970-01-01 08:00:00 |
| 1999-12-31 09:00:00 | 946602000 | 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 | 960104730 | 2000-06-04 15:45:30 |
| 2017-03-16 12:30:15 | 1489638615 | 2017-03-16 12:30:15 |
+---------------------+------------+---------------------+
2.5 计算时间间隔
使用DATEDIFF
函数可以计算两个日期的间隔,单位是天:
mysql> SET @d1 = '2010-01-01', @d2 = '2009-12-01';
mysql> SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';
+---------+---------+
| d1 - d2 | d2 - d1 |
+---------+---------+
| 31 | -31 |
+---------+---------+
注意是第一个参数减去第二个。
TIMEDIFF
函数计算两个时间值的间隔:
mysql> SET @t1 = '12:00:00', @t2 = '16:30:00';
mysql> SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';
+------------------+-----------------+
| t1 - t2 | t2 - t1 |
+------------------+-----------------+
| -04:30:00.000000 | 04:30:00.000000 |
+------------------+-----------------+
TIMESTAMPDIFF是一个更强大的函数,可以计算两个包含日期与时间参数之间的间隔,还可以自定义单位:
mysql> SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';
mysql> SELECT
-> TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,
-> TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,
-> TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,
-> TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,
-> TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;
+---------+-------+------+-------+-------+
| minutes | hours | days | weeks | years |
+---------+-------+------+-------+-------+
| 5258880 | 87648 | 3652 | 521 | 10 |
+---------+-------+------+-------+-------+
2.6 时间的加减
将time加到time或date-and-time值上:
mysql> SET @t1 = '12:00:00', @t2 = '15:30:00';
mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';
mysql> SELECT ADDTIME(@t1,@t2), ADDTIME(@dt,@t);
+------------------+---------------------+
| ADDTIME(@t1,@t2) | ADDTIME(@dt,@t) |
+------------------+---------------------+
| 27:30:00 | 1984-03-02 00:00:00 |
+------------------+---------------------+
将time加到date或date-and-time值上:
mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';
mysql> SELECT TIMESTAMP(@d,@t), TIMESTAMP(@dt,@t);
+----------------------------+----------------------------+
| TIMESTAMP(@d,@t) | TIMESTAMP(@dt,@t) |
+----------------------------+----------------------------+
| 1984-03-01 12:00:00.000000 | 1984-03-02 00:00:00.000000 |
+----------------------------+----------------------------+
还可以使用DATE_ADD
或DATE_SUB
完成更复杂的加减:
mysql> SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL 1 WEEK);
+------------+-------------------------------------+
| CURDATE() | DATE_SUB(CURDATE(),INTERVAL 1 WEEK) |
+------------+-------------------------------------+
| 2022-08-30 | 2022-08-23 |
+------------+-------------------------------------+
三天四小时之后:
mysql> SELECT NOW(), DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR);
+---------------------+-----------------------------------------+
| NOW() | DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR) |
+---------------------+-----------------------------------------+
| 2022-08-30 22:40:47 | 2022-09-03 02:40:47 |
+---------------------+-----------------------------------------+
还可以直接使用INTERVAL
操作符:
mysql> SELECT NOW(), NOW() - INTERVAL '1 12' DAY_HOUR;
+---------------------+----------------------------------+
| NOW() | NOW() - INTERVAL '1 12' DAY_HOUR |
+---------------------+----------------------------------+
| 2022-08-30 22:41:54 | 2022-08-29 10:41:54 |
+---------------------+----------------------------------+
2.7 获取特定的日期
通过上面的函数,可以得到一些有用的时间。
获取给定日期所在月的第一天:
mysql> SELECT d, DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY) AS '1st of month' FROM date_val;
+------------+--------------+
| d | 1st of month |
+------------+--------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 |
+------------+--------------+
所在月的最后一天(直接使用LAST_DAY
函数):
mysql> SELECT d, LAST_DAY(d) AS 'last of month' FROM date_val;
+------------+---------------+
| d | last of month |
+------------+---------------+
| 1864-02-28 | 1864-02-29 |
| 1900-01-15 | 1900-01-31 |
| 1999-12-31 | 1999-12-31 |
| 2000-06-04 | 2000-06-30 |
| 2017-03-16 | 2017-03-31 |
+------------+---------------+
返回当月的天数:
mysql> SELECT d, DAYOFMONTH(LAST_DAY(d)) AS 'days in month' FROM date_val;
+------------+---------------+
| d | days in month |
+------------+---------------+
| 1864-02-28 | 29 |
| 1900-01-15 | 31 |
| 1999-12-31 | 31 |
| 2000-06-04 | 30 |
| 2017-03-16 | 31 |
+------------+---------------+
2.8 星期相关
函数DAYNAME()
返回星期:
mysql> SELECT CURDATE(), DAYNAME(CURDATE());
+------------+--------------------+
| CURDATE() | DAYNAME(CURDATE()) |
+------------+--------------------+
| 2022-08-30 | Tuesday |
+------------+--------------------+
1 row in set (0.03 sec)
函数DAYOFWEEK()
返回从周日开始的数值,范围是1-7:
mysql> SELECT CURDATE(), DAYNAME(CURDATE()), DAYOFWEEK(CURDATE());
+------------+--------------------+----------------------+
| CURDATE() | DAYNAME(CURDATE()) | DAYOFWEEK(CURDATE()) |
+------------+--------------------+----------------------+
| 2022-08-30 | Tuesday | 3 |
+------------+--------------------+----------------------+
周日是1,所以周二是3。
函数WEEKDAY()
也返回一个数值,只是从周一开始,范围是0-6:
mysql> SELECT CURDATE(), DAYNAME(CURDATE()), WEEKDAY(CURDATE());
+------------+--------------------+--------------------+
| CURDATE() | DAYNAME(CURDATE()) | WEEKDAY(CURDATE()) |
+------------+--------------------+--------------------+
| 2022-08-30 | Tuesday | 1 |
+------------+--------------------+--------------------+
周一是0,所以周二是1。这个在应用中是比较常用的,比如给定日期,返回所在周的周一:
mysql> SET @d="2022-03-08";
mysql> SELECT @d, DATE_SUB(@d, INTERVAL WEEKDAY(@d) DAY) AS Monday;
+------------+------------+
| @d | Monday |
+------------+------------+
| 2022-03-08 | 2022-03-07 |
+------------+------------+
3. 时间类型实践
接下来我们从实践的角度,看看时间类型的使用。
3.1 时间类型的选择
MySQL中的五种时间类型,在我们的应用中该如何选择呢?
使用场景最多的是DATETIME
和TIMESTAMP
,它们能存储日期与时间,还可以选择更细粒度的小数秒,最高精度微秒。
因此如果应用中需要日期与时间,那么只能从这俩里选择了。
但是TIMESTAMP
有2038问题,如果这个不成问题,那么使用TIMESTAMP
是一个不错的选择。
我们甚至可以使用一个BIGINT
来存储时间戳,这就避免了2038问题,但是随之而来的就是需要我们自己来管理时间,包括非法值,以及显示、转换等。
而YEAR
、TIME
和DATE
一般使用的不是很多。
有些场景需要保存更细粒度的小数秒,具体精确到多少还是需要结合实际场景考虑。
3.2 代码中时间的映射
代码中我们使用MySQL的driver来解析,对于go来说就是https://github.com/go-sql-driver/mysql
在连接数据库的时候,和时间相关的参数有loc
和parseTime
,其中loc
是时区,parseTime
表示是否解析时间。
如果parseTime
为true,那么就会将MySQL中的时间类型的值解析成go里的time.Time
,否则返回的是字符串。
loc
参数指定了程序与服务器连接时的时区,默认是UTC。
下面的代码片段展示了mysql driver从服务器返回值解析时间的过程(见https://github.com/go-sql-driver/mysql/blob/master/utils.go#L108):
func parseDateTime(b []byte, loc *time.Location) (time.Time, error) {
const base = "0000-00-00 00:00:00.000000"
switch len(b) {
case 10, 19, 21, 22, 23, 24, 25, 26: // up to "YYYY-MM-DD HH:MM:SS.MMMMMM"
if string(b) == base[:len(b)] {
return time.Time{}, nil
}
year, err := parseByteYear(b)
if err != nil {
return time.Time{}, err
}
if year <= 0 {
year = 1
}
...
return time.Date(year, month, day, hour, min, sec, nsec, loc), nil
default:
return time.Time{}, fmt.Errorf("invalid time bytes: %s", b)
}
}
而parseDateTime
函数是在这里调用的(见https://github.com/go-sql-driver/mysql/blob/master/packets.go#L736):
func (rows *textRows) readRow(dest []driver.Value) error {
...
for i := range dest {
// Read bytes and convert to string
dest[i], isNull, n, err = readLengthEncodedString(data[pos:])
pos += n
...
if !mc.parseTime {
continue
}
// Parse time field
switch rows.rs.columns[i].fieldType {
case fieldTypeTimestamp,
fieldTypeDateTime,
fieldTypeDate,
fieldTypeNewDate:
if dest[i], err = parseDateTime(dest[i].([]byte), mc.cfg.Loc); err != nil {
return err
}
}
}
return nil
}
使用的就是配置中的Loc来解析时间。
mysql driver写入时间值也是同样的道理,代码在https://github.com/go-sql-driver/mysql/blob/master/utils.go#L278
而在日常开发中,我们使用orm处理数据库更多一些,以xorm为例,xorm使用TZLocation
和DatabaseTZ
来处理时区(见https://gitea.com/xorm/xorm/src/branch/master/engine.go#L33):
type Engine struct {
...
TZLocation *time.Location // The timezone of the application
DatabaseTZ *time.Location // The timezone of the database
...
}
写入时间时,需要将go的time.Time转成字符串来写入数据库(见https://gitea.com/xorm/xorm/src/branch/master/dialects/time.go#L15):
func FormatColumnTime(dialect Dialect, dbLocation *time.Location, col *schemas.Column, t time.Time) (interface{}, error) {
...
switch col.SQLType.Name {
case schemas.Date:
return t.Format("2006-01-02"), nil
case schemas.Time:
layout := "15:04:05"
if col.Length > 0 {
// we can use int(...) casting here as it's very unlikely to a huge sized field
layout += "." + strings.Repeat("0", int(col.Length))
}
return t.Format(layout), nil
case schemas.DateTime, schemas.TimeStamp:
layout := "2006-01-02 15:04:05"
if col.Length > 0 {
// we can use int(...) casting here as it's very unlikely to a huge sized field
layout += "." + strings.Repeat("0", int(col.Length))
}
return t.Format(layout), nil
case schemas.Varchar:
return t.Format("2006-01-02 15:04:05"), nil
case schemas.TimeStampz:
if dialect.URI().DBType == schemas.MSSQL {
return t.Format("2006-01-02T15:04:05.9999999Z07:00"), nil
} else {
return t.Format(time.RFC3339Nano), nil
}
case schemas.BigInt, schemas.Int:
return t.Unix(), nil
default:
return t, nil
}
}
从数据库读取时,将字符串转成time.Time(见https://gitea.com/xorm/xorm/src/branch/master/convert/time.go#L18):
// String2Time converts a string to time with original location
func String2Time(s string, originalLocation *time.Location, convertedLocation *time.Location) (*time.Time, error) {
if len(s) == 19 {
if s == utils.ZeroTime0 || s == utils.ZeroTime1 {
return &time.Time{}, nil
}
dt, err := time.ParseInLocation("2006-01-02 15:04:05", s, originalLocation)
if err != nil {
return nil, err
}
dt = dt.In(convertedLocation)
return &dt, nil
}
...
return nil, fmt.Errorf("unsupported conversion from %s to time", s)
}
因此,我们在使用orm处理数据库时,只需要配置好各自orm的配置就可以了。
3.3 时间的序列化
将时间序列化成字符串返回给前端进行展示是应用中经常使用到的。在不涉及到不同时区的场景下,直接返回YYYY-MM-DD hh:mm:ss
格式即可。
在需要使用不同时区的国际化场景中,建议序列化的字符串中带上时区信息,由前端根据用户所处的时区进行展示。
比如:2022-08-20T12:09:08+08:00