MySQL数据类型-日期和时间类型 (DATETIME DATE等)

偏执的太偏执、 2023-01-07 12:27 154阅读 0赞

MySQL提供日期和时间的类型以及日期和时间的组合，表示时间值的日期和时间类型为`DATETIME`、`DATE`、`TIMESTAMP`、`TIME`和`YEAR`。

**每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值，并将零值插入到数据库中。**

`TIMESTAMP`类型有专有的自动更新特性

### 日期和时间类型 ###

参考 [MySQL5.7 Date and Time Data Type Syntax][]

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小 ( bytes)</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>用途</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>YEAR</td> 
   <td>1个字节</td> 
   <td>1901 ~ 2155 或 0000</td> 
   <td>YYYY</td> 
   <td>年份值<br>YEAR(4)</td> 
   <td>YEAR(2)不推荐使用 该数据类型，并且在MySQL 5.7.5中删除了对该数据类型的支持<br>允许使用字符串‘2021’或数字2021将值插入数据库<br>查询时值可使用字符串‘2021’或数字2021</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3个字节</td> 
   <td>‘-838:59:59.000000’ ~ ‘838:59:59.000000’</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间<br>TIME[(<em><code>fsp</code></em>)]</td> 
   <td>允许使用字符串‘10:11:12’、‘101112’或数字101112将值插入数据库或查询<br>[]为可选，fsp可以指定一个介于0到6之间的可选值，以指定小数秒精度。值为0表示没有小数部分。如果省略，则默认精度为0</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3个字节</td> 
   <td>1000-01-01 ~ 9999-12-31</td> 
   <td>YYYY-MM-DD</td> 
   <td>日期值</td> 
   <td>允许使用字符串‘2020-02-02’、‘20200202’或数字20200202将值插入数据库或查询</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8个字节</td> 
   <td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
   <td>允许使用字符串’2008-08-06 18:16:18’、'20080807181718’或数字20080808181818将值插入数据库或查询</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4个字节</td> 
   <td>‘1970-01-01 00:00:01.000000’ UTC ~ ‘2038-01-19 03:14:07.999999’ UTC</td> 
   <td>YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
   <td>与DATETIME相似，具体看下面描述</td> 
  </tr> 
 </tbody> 
</table>

### YEAR 类型 ###

`YEAR` 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

*  如果只需要记录年份，则使用 YEAR 类型即可
 *  以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 '1901’～’2155’或 ‘0000’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。
 *  以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。 **YEAR(2)该数据类型已经不推荐使用**。
 *  以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。**该YEAR(2)数据类型已经不推荐使用**。

> 提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

### TIME 类型 ###

`TIME` 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

如果我们想插入当前系统的时间，则可以插入`CURRENT_TIME`或者`NOW()`

可以使用各种格式指定 TIME 值，如下所示。

*  如果只记录时间，可以使用 TIME 类型
 *  ‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D\*24+HH”。
 *  ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

> 提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12。

### DATE 类型 ###

`DATE` 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

*  以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。
 *  以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘20002069’，'7099’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。
 *  以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
 *  使用 CURRENT\_DATE 或者 NOW()，插入当前系统日期。

> 提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

### DATETIME 类型 ###

`DATETIME` 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH：MM：SS’，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

*  如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型，由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME
 *  以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
 *  以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
 *  以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

> 提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 11*30*45’ 和 ‘98@12@31 113045’ 是等价的，这些值都可以正确地插入数据库。

### TIMESTAMP 类型 ###

`TIMESTAMP` 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 **TIMESTAMP 列的取值范围小于 DATETIME 的取值范围**，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

TIMESTAMP的数值是与时区相关，即UTC

> 提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP类型如何插入当前时间：

*  可以使用`CURRENT_TIMESTAMP`
 *  输入NULL，系统自动输入当前的TIMESTAMP （建表时候加上：t\_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT\_TIMESTAMP,）
 *  `UPDATE` table `SET` t\_timestamp=CURRENT\_TIMESTAMP

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

*  DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；**推荐**
 *  而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

> 提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

[MySQL5.7 Date and Time Data Type Syntax]: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html