MySQL日期和时间数据类型

短命女 2024-03-16 14:13 68阅读 0赞

用于表示时间值的日期和时间数据类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR。每种时间类型都有一组有效值的范围，以及一个“零”值，当您指定MySQL无法表示的无效值时，可以使用该值。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:247px;">Data Type</td> 
   <td style="width:251px;">“Zero” Value</td> 
  </tr> 
  <tr> 
   <td style="width:247px;">DATE</td> 
   <td style="width:251px;">'0000-00-00'</td> 
  </tr> 
  <tr> 
   <td style="width:247px;">TIME</td> 
   <td style="width:251px;">'00:00:00'</td> 
  </tr> 
  <tr> 
   <td style="width:247px;">DATETIME</td> 
   <td style="width:251px;">'0000-00-00 00:00:00'</td> 
  </tr> 
  <tr> 
   <td style="width:247px;">TIMESTAMP</td> 
   <td style="width:251px;">'0000-00-00 00:00:00'</td> 
  </tr> 
  <tr> 
   <td style="width:247px;">YEAR</td> 
   <td style="width:251px;">0000</td> 
  </tr> 
 </tbody> 
</table>

在使用日期和时间类型时，需要注意以下事项：

*  如果在数值上下文中使用日期或时间值，MySQL会自动将其转换为数字，反之亦然。
 *  默认情况下，当MySQL遇到超出范围或其他类型无效的日期或时间类型值时，它将该值转换为该类型的“零”值。
 *  MySQL允许您将值为“0000-00-00”的“零”值存储为“虚拟日期”。在某些情况下，这比使用NULL值更方便，并且使用的数据和索引空间更少。

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性。

#### DATE ####

日期支持的范围是“1000-01-01”到“9999-12-31”。MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字将值分配给DATE列。

#### DATETIME ####

日期和时间组合。支持的范围是“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。MySQL以“YYYY-MM-DD hh:mm:ss\[.fraction\]”格式显示DATETIME值，但允许使用字符串或数字将值分配给DATETIME列。

#### TIMESTAMP ####

时间戳。范围是“1970-01-01 00:00:01.000000” UTC到“2038-01-19 03:14:07.999999” UTC。TIMESTAMP值以自纪元（“1970-01-01 00:00:00” UTC）以来的秒数存储。TIMESTAMP不能表示值“1970-01-01 00:00:00”，因为它相当于自纪元以来的0秒，值0保留用于表示“0000-00-00 00:00:00”的“零”TIMESTAMP值。

服务器处理TIMESTAMP定义的方式取决于explicit\_defaults\_for\_timestamp系统变量的值。

#### TIME ####

时间。范围是“-838:59:59.000000”到“838:59:59.000000”。MySQL以“hh:mm:ss\[.fraction\]”格式显示TIME值，但允许使用字符串或数字将值分配给TIME列。

#### YEAR ####

4位数字格式的年份。MySQL以YYYY格式显示YEAR值，但允许使用字符串或数字将值分配给YEAR列。值显示为1901到2155或0000。

SUM()和AVG()聚合函数不适用于时间值。（它们将值转换为数字，失去第一个非数字字符之后的所有内容。）为解决这个问题，请将其转换为数字单位，执行聚合操作，然后再将其转换回时间值。

DATE类型用于具有日期部分但没有时间部分的值。MySQL以“YYYY-MM-DD”格式检索和显示DATE值。支持的范围是“1000-01-01”到“9999-12-31”。

DATETIME类型用于包含日期和时间部分的值。MySQL以“YYYY-MM-DD hh:mm:ss”格式检索和显示DATETIME值。支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。

TIMESTAMP数据类型用于包含日期和时间部分的值。TIMESTAMP的范围是“1970-01-01 00:00:01” UTC到“2038-01-19 03:14:07” UTC。

DATETIME或TIMESTAMP值可以包括高达微秒（6位数字）精度的尾随小数秒部分。特别地，插入到DATETIME或TIMESTAMP列中的值中的任何小数部分都会被存储而不是丢弃。包括小数部分时，这些值的格式为“YYYY-MM-DD hh:mm:ss\[.fraction\]”，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”。小数部分应始终由一个小数点与其余的时间分开；不识别其他小数秒分隔符。

MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，然后从UTC转换回当前时区进行检索（对于其他类型如DATETIME则不会发生此操作）。默认情况下，每个连接的当前时区是服务器的时间。可以按每个连接设置时区。只要时区设置保持不变，您就会得到存储的相同值。如果存储一个TIMESTAMP值，然后更改时区并检索该值，则检索到的值与存储的值不同。这是因为在两个方向上未使用相同的时区进行转换。当前时区可用作time\_zone系统变量的值。

在MySQL 8.0.22及更高版本中，可以使用CAST()和AT TIME ZONE运算符将TIMESTAMP值转换为UTC DATETIME值，如下所示：

mysql> SELECT col,
    
     > CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut
    
     > FROM ts ORDER BY id;
    
    +---------------------+---------------------+
    | col                 | ut                  |
    +---------------------+---------------------+
    | 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
    | 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
    | 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
    | 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
    | 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
    | 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
    +---------------------+---------------------+

MySQL允许使用任何标点符号字符作为日期部分或时间部分之间的分隔符的字符串指定值的“放松”格式。在某些情况下，此语法可能会误导。例如，像'10:11:12'这样的值可能看起来像一个时间值，因为有:，但如果在日期上下文中使用，则会解释为年份“2010-11-12”。值'10:45:15'将被转换为'0000-00-00'，因为'45'不是有效的月份。

服务器要求月份和日期值有效，而不仅仅是分别在1到12和1到31之间的范围内。在禁用严格模式的情况下，无效日期（例如'2004-04-31'）将被转换为'0000-00-00'并生成警告。启用严格模式时，无效日期会生成错误。要允许这些日期，请启用ALLOW\_INVALID\_DATES。

MySQL不接受包含零的日或月列的TIMESTAMP值，也不接受无效日期值。唯一的例外是特殊的“零”值'0000-00-00 00:00:00'，如果SQL模式允许此值。精确的行为取决于是否启用了严格SQL模式和NO\_ZERO\_DATE SQL模式中的任何一个。