mysql的日期时间类型及精度问题/time、date、datetime、timestamp和year

一、 前言

本文主要整理mysql提供的五种日期时间类型time、date、datetime、timestamp和year的区别，及日期时间类型的精度问题

一次线上bug, mysql数据类型为timestamp，日志中打印的时间为2021-02-23 10:16:55.781 但是最后数据库中存储的是2021-02-23 10:16:56，由此引发了本文对timestamp精度问题的研究。

以下内容基于MySQL8.0.23版本进行讲解

二、 mysql中的日期时间类型

mysql中的日期时间类型有time、date、datetime、timestamp和year

数据类型	最小值	最大值	零值表示
date	1000-01-01	9999-12-31	0000-00-00
datetime	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00[.000000]
time	-838:59:59	838:59:59	00:00:00[.000000]
timestamp	19700101080001	2038-01-19 03:14:07.999999(准确的来讲应该是UTC范围)	0000000000000000[000000]
year	1901	2155	0000

每种日期和时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错。
对于数据类型TIME、DATETIME和TIMESTAMP，MySQL 5.6.4增加了对小数秒(fsp：[fractional seconds storage])的支持,小数秒可以有6位(微秒)精度

mysql会对每个数据类型的有效性进行检测，不可以存储不正确、非法的日期，如date类型不可以存储2024-02-31这个不存在的日期；datetime中的时分秒不能超过23:59:59…等等

2.1、详细解释

date: yyyy-mm-dd格式表示的日期值，date用于表示年月日，如果实际应用值需要保存年月日就可以使用date。

• MySQL数据库在存储时会校验date类型的正确性。①：必须是yyyy-MM-dd格式 ②：必须是合法日期，不能是不存在的日期，如2024-02-31

datetime: yyyy-mm-dd hh:mm:ss格式，datetime用于表示年月日时分秒，是 date和time的组合，并且记录的年份（见上表）比较长久。

time: hh:mm:ss格式表示的时间值, time用于表示时分秒(有负时间表示)

timestamp: timestamp用于表示年月日时分秒，但是记录的年份（见上表）比较短暂最大只能到2038-01-19 03:14:07.999999(准确的来讲应该是UTC范围)。timestamp和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。
timestamp的属性受 Mysql 版本和服务器 SQLMode 的影响较大。
如果记录的日期需要让不同时区的人使用，最好使用timestamp。

year: year用于表示年份，year 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存year 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

2.2、占用字节

在MySQL 5.6版本里，对这些类型进行了多项重要的改进：
对于数据类型TIME、DATETIME和TIMESTAMP，MySQL 5.6.4增加了对小数秒(fsp：[fractional seconds storage])的支持。 这些类型现在允许的可选小数部分多达6位(微秒)精度。
MySQL 5.6.5引入了扩展支持：自动把当前时间戳作为初始值并进行更新。在以前的版本里，这些属性只能用于表里的大部分单个TIMESTAMP列。现在，它们可以用于任何 TIMESTAMP列，并且也可用于DATETIME列。
MySQL 5.6.6丢弃了对YEAR(2)的支持，取而代之的是允许创建像YEAR(4)那样的列。

如果要声明包含小数秒部分的时态类型列，则需要把定义写成type_name(fsp)，其中，type_name为TIME、DATETIME或TIMESTAMP，fsp为小数秒精度。例如，下面的TIME列允许 的小数位数分别为3位和6位：

CREATE TABLE `date_test` (
  `d3` time(3),
  `d4` time(6)
) ENGINE=InnoDB AUTO_INCREMENT=0  COMMENT='test测试';

fsp值的取值范围必须为0~6。如果未给定，则默认为0。更多相关信息在下面会介绍。
下图展示的是每一种数据类型的存储空间要求：

下图展示的是那些声明中带有小数秒部分的类型所具有的额外存储空间要求：

2.3、time类型

time类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示Time类型的值。其中，HH表示时；MM表示分，取值范围为0 ~~ 59；SS表示秒，取值范围是0 ~~ 59。
Time类型的范围可以从‘-838：59：59’ ~~ ‘838：59：59’。虽然，小时的范围是0~~23，但是为了表示某种特殊需要的时间间隔，将Time类型的范围扩大了。而且还支持了负值。

Time类型的字段赋值的表示方法如下：
1，‘D HH:MM:SS’格式的字符串表示。其中，D表示天数，取值范围是0~~34。保存时，小时的值等于（D*24+HH）。举个例子，输入‘2 11：30：50’，Time类型会转换为59：30：50。
当然。输入时可以不严格按照这个格式，也可以是‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,'SS’等形式。举个例子，输入‘30’，Time类型会自动转换为00：00：30。

2，‘HHMMSS’格式的字符串或HHMMSS格式的数值表示，例如，输入‘123456’，Time类型会转换成12：34：56；输入123456，Time类型会转换成12：34：56。如果输入0或者‘0’，那么TIME类型会转换为0000：00：00。

3，使用current_time或者current_time()或者now()输入当前系统时间。

三、datetime和timestamp区别

3.1、相同点

datetime和timestamp都可以表示 YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据。

并且从MySQL5.6.4之后这两者都可以包含秒后的小数部分，精度最高为微妙（6位）。

这里有一个点需要注意，就是在MySQL5.6.4之前，这两个是都表示不了小数的。

3.2、不同点

1：存储范围不同

datetime的存储范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999，而timestamp的范围是 1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999(准确的来讲应该是UTC范围)

2：时区相关

• datetime存储与时区无关（准备来说是datetime只支持一个时区，就是存储时当前服务器的时区），而timestamp存储的是与时区有关。
• MySQL在存储timestamp时，会先将时间从当前服务器的时区转换为UTC（世界协调时）以进行存储，然后查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库的时区而发生改变。而datetime的存储则与时区无关，数据是什么就存储什么，也就返回什么。

timestamp 更适合来记录时间，比如我在东八区时间现在是 2021-06-08 10:23:45， 你在日本（东九区此时时间为 2021-06-08 11:23:45），我和你在聊天，数据库记录了时间，取出来之后，对于我来说时间是 2021-06-08 10:23:45，对于日本的你来说就是 2021-06-08 11:23:45。所以就不用考虑时区的计算了。

3:存储空间大小

在5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节；但是在5.6.4之后，由于这两个类型允许有小数部分，所以占用的存储空间和以前不同；MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。

四、mysql日期类型中的坑

4.1、 time、timestamp、datetime数据类型四舍五入

当前mysql版本为8.0.18

CREATE TABLE `date_test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `d1` date,
  `d2` datetime,
  `d3` time,
  `d4` timestamp,
  `d5` year,
  primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=0  COMMENT='test测试';

insert into date_test(d2,d4) values('2021-02-23 10:16:55.781','2021-02-23 10:16:55.781');

结论： 当time、timestamp、datetime数据类型不指定精度时默认会四舍五入
问题解决： 可以设置字段的精度，如timestamp(3)、timestamp(6)

五、其他疑问问题

5.1、date类型可以存时分秒么？可以存储不存在的日期么？

date类型只能存储yyyy-mm-dd格式的日期，不能存储时分秒字段；
date类型只能存储合法的日期，即必须存在的日期，不能是非法日期，如2024–02-30是不存在的，不可以存储

示例：

CREATE TABLE `date_test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `d1` date,
  `d2` datetime,
  `d3` time,
  `d4` timestamp,
  `d5` year,
  primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=0  COMMENT='test测试';

select * from date_test;


insert into date_test(d1) values('2024-02-30');