MySQL中的时间类型

时间是一类重要的数据，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可以设置自动初始化与更新：

代码语言：sql复制

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的值，那它们默认就是当前时间。

更新记录的时候，也会更新为当前时间。

一般来说我们会创建两个时间字段，一个用于记录创建时间，一个用于记录更新时间：

代码语言：sql复制

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模式）：

代码语言：sql复制

mysql> select @@global.sql_modeG
*************************** 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中是开启的。

可以查看这个变量的值：

代码语言：sql复制

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会报错：

代码语言：sql复制

mysql> create table t_null (ts timestamp default null);
ERROR 1067 (42000): Invalid default value for 'ts'

• 如果没有显式设置NULL，默认值或ON UPDATE属性，那么第一个TIMESTAMP字段会有自动初始化与自动更新属性：

代码语言：sql复制

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时间戳存储；当客户端查询时，再转换成客户端时区的值；
• 其它时间相关的类型没有时区信息。

查看当前时区：

代码语言：sql复制

mysql> SELECT @@global.time_zone, @@session.time_zone;
 -------------------- --------------------- 
| @@global.time_zone | @@session.time_zone |
 -------------------- --------------------- 
| SYSTEM             | SYSTEM              |
 -------------------- --------------------- 

SYSTEM意味着服务器的时区和系统保持一致。

假如SYSTEM是东八区，一个处于同时区的客户端C1已经存储了下面的数据：

代码语言：sql复制

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东八区，这样这个客户端查询存储的时间是有问题的：

代码语言：sql复制

mysql> select * from ts;
 --------------------- 
| ts                  |
 --------------------- 
| 2022-08-30 14:13:02 |
 --------------------- 

C2应该设置自己的时区来显示相应的时间：

代码语言：sql复制

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), day = n % 32；这种形式比年月日分别一个字节存储扩大了范围，毕竟月最大12而日最大31，使用一个字节存储有点浪费；
• TIME：也是三个字节的数字，这个数字由DD*24*3600 HH*3600 MM*60 SS得到。比如25:10:20，得到90620，反过来也可以得到对应的时分秒：h = n/3600, m = (n/60)`, s = n`；
• TIMESTAMP：四个字节的数字，用于储存时间戳，这个就比较简单了；
• DATETIME：八个字节的数字，四个字节的数字用于表示日期：YYYY*10000 MM*100 DD，四个字节的数字用于表示时间：HH*10000 MM*100 SS。同样，可以通过数字反向得到对应的具体时间。

添加小数秒的支持后，支持小数秒的TIME，TIMESTAMP和DATETIME存储发生了变化，使用大端序进行储存，跟着可选的小数秒，同时DATETIME也进行了优化。而YEAR和DATE没有变化。

• TIME无小数秒部分：

代码语言：shell复制

 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无小数秒部分：

代码语言：shell复制

 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()的别名。

代码语言：sql复制

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构建时间：

代码语言：sql复制

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进行部分替换：

代码语言：sql复制

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进行部分替换：

代码语言：sql复制

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的拼接：

代码语言：sql复制

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()：

代码语言：sql复制

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()：

代码语言：sql复制

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：

代码语言：sql复制

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函数可以计算两个日期的间隔，单位是天：

代码语言：sql复制

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函数计算两个时间值的间隔：

代码语言：sql复制

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是一个更强大的函数，可以计算两个包含日期与时间参数之间的间隔，还可以自定义单位：

代码语言：sql复制

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值上：

代码语言：sql复制

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值上：

代码语言：sql复制

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完成更复杂的加减：

代码语言：sql复制

mysql> SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL 1 WEEK);
 ------------ ------------------------------------- 
| CURDATE()  | DATE_SUB(CURDATE(),INTERVAL 1 WEEK) |
 ------------ ------------------------------------- 
| 2022-08-30 | 2022-08-23                          |
 ------------ ------------------------------------- 

三天四小时之后：

代码语言：sql复制

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操作符：

代码语言：sql复制

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 获取特定的日期

通过上面的函数，可以得到一些有用的时间。

获取给定日期所在月的第一天：

代码语言：sql复制

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函数)：

代码语言：sql复制

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    |
 ------------ --------------- 

返回当月的天数：

代码语言：sql复制

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()返回星期：

代码语言：sql复制

mysql> SELECT CURDATE(), DAYNAME(CURDATE());
 ------------ -------------------- 
| CURDATE()  | DAYNAME(CURDATE()) |
 ------------ -------------------- 
| 2022-08-30 | Tuesday            |
 ------------ -------------------- 
1 row in set (0.03 sec)

函数DAYOFWEEK()返回从周日开始的数值，范围是1-7：

代码语言：sql复制

mysql> SELECT CURDATE(), DAYNAME(CURDATE()), DAYOFWEEK(CURDATE());
 ------------ -------------------- ---------------------- 
| CURDATE()  | DAYNAME(CURDATE()) | DAYOFWEEK(CURDATE()) |
 ------------ -------------------- ---------------------- 
| 2022-08-30 | Tuesday            |                    3 |
 ------------ -------------------- ---------------------- 

周日是1，所以周二是3。

函数WEEKDAY()也返回一个数值，只是从周一开始，范围是0-6：

代码语言：sql复制

mysql> SELECT CURDATE(), DAYNAME(CURDATE()), WEEKDAY(CURDATE());
 ------------ -------------------- -------------------- 
| CURDATE()  | DAYNAME(CURDATE()) | WEEKDAY(CURDATE()) |
 ------------ -------------------- -------------------- 
| 2022-08-30 | Tuesday            |                  1 |
 ------------ -------------------- -------------------- 

周一是0，所以周二是1。这个在应用中是比较常用的，比如给定日期，返回所在周的周一：

代码语言：sql复制

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)：

代码语言：go复制

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)：

代码语言：go复制

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)：

代码语言：go复制

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)：

代码语言：go复制

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):

代码语言：go复制

// 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

云数据库SQLServermysql datetime

0 人点赞