mysql的日期时间类型及精度问题

你的名字 2023-10-05 22:45 12阅读 0赞

#### 文章目录 ####

*   *  前言
     *  mysql中的日期时间类型
     *  占用字节
     *  time类型
     *  datetime和timestamp区别
     *   *  一、相同点
         *  二、不同点
     *  mysql日期类型中的坑
     *   *  time、timestamp、datetime数据类型四舍五入

### 前言 ###

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

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

### mysql中的日期时间类型 ###

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

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

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

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

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 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

### 占用字节 ###

在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。更多相关信息在下面会介绍。  
下图展示的是每一种数据类型的存储空间要求：  
![在这里插入图片描述][38b2fd56e9104be5b9f834832bd48bf4.png]  
下图展示的是那些声明中带有小数秒部分的类型所具有的额外存储空间要求：  
![在这里插入图片描述][3b4bdb0558fb403cbdfb89e99696b9b6.png]

### 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区别 ###

#### 一、相同点 ####

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

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

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

#### 二、不同点 ####

**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个字节，具体取决于存储值的小数秒精度。

![在这里插入图片描述][38b2fd56e9104be5b9f834832bd48bf4.png]  
![在这里插入图片描述][3b4bdb0558fb403cbdfb89e99696b9b6.png]

### mysql日期类型中的坑 ###

#### 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');

![在这里插入图片描述][77092669f1284c2d9d78435002042216.png]  
**结论：** 当time、timestamp、datetime数据类型不指定精度时默认会四舍五入  
**问题解决：** 可以设置字段的精度，如timestamp(3)、timestamp(6)

[38b2fd56e9104be5b9f834832bd48bf4.png]: https://img-blog.csdnimg.cn/38b2fd56e9104be5b9f834832bd48bf4.png
[3b4bdb0558fb403cbdfb89e99696b9b6.png]: https://img-blog.csdnimg.cn/3b4bdb0558fb403cbdfb89e99696b9b6.png
[77092669f1284c2d9d78435002042216.png]: https://img-blog.csdnimg.cn/77092669f1284c2d9d78435002042216.png