像程序员一样使用MySQL最新章节_黄文毅著

3.2　日期和时间类型

日期和时间类型也是非常重要的知识点，几乎所有的表都会用到。比如，第2章中animal表的创建时间create_time和更新时间update_time就是日期时间类型（datetime）。其他类型有date（日期）、time（时间）、timestamp（时间戳）和year（年），具体如表3-2所示。

表3-2　日期和时间类型

从表3-2可知，不同的类型，其数据格式、取值范围以及占用内存空间大小各不相同。建议读者在真实的工作中，尽量使用日期时间类型（datetime），虽然它占用的字节最多，但可同时表示日期和时间，使用起来也比较方便。

提示允许使用字符串或数字将值分配给日期和时间类型，比如将字符串“2022-11-11 12:00:00”赋值给datetime类型。

3.2.1　时间小数秒精确度

MySQL允许time、datetime和timestamp值的小数秒精度高达微秒（6位）。若要定义包含秒小数部分的列，则需要使用语法type_name（fsp），其中type_name是时间、日期时间或时间戳，fsp是秒的小数部分精度。例如：

     -- 指定小数秒精确度
     mysql> create table t1 (t time(3), dt datetime(6), ts timestamp(0));

这个MySQL语句用于创建一张名为t1的表，该表包含3个字段：t、dt和ts。这3个字段的数据类型分别为time(3)、datetime(6)和timestamp(0)。

t：数据类型为time，表示时间，精确到秒。这里的数字3表示小数点后的位数，即保留3位小数。
dt：数据类型为datetime，表示日期和时间，精确到微秒。这里的数字6表示小数点后的位数，即保留6位小数。
ts：数据类型为timestamp，表示日期和时间，精确到毫秒。这里的数字0表示不保留小数位。

如果给定fsp值，则必须在0到6的范围内，值为0表示没有小数部分。如果省略，则默认精度为0。

如果将带有秒小数部分的time、date或timestamp值插入相同类型但小数位数较少的列中，则会导致舍入，例如：

     mysql> create table fractest( c1 time(2), c2 datetime(2), c3 timestamp(2) );
     mysql> insert into fractest values
     ('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

以舍入方式插入表中：

提示发生此类舍入时不会给出警告或错误。此行为遵循SQL标准。

如果不期望插入的值发生舍入，而是直接截断，那么可以启用TIME_TRUNCATE_FRACTIONAL SQL模式：

     SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

启用该SQL模式后，插入时发生截断：

3.2.2　日期和时间类型转换

在某种程度上，可以将值从一种类型转换为另一种类型，但是，可能会丢失一部分信息。通常类型之间的转换都受结果类型的有效值范围的约束。例如，尽管可以使用同一组格式指定date、datetime和timestamp值，但这些类型并不都具有相同的值范围，时间戳（timestamp）的值不能早于1970 UTC或晚于2038-01-19 03：14：07 UTC，这意味着诸如1968-01-01之类的日期虽然作为date或datetime值有效，但作为timestamp值则无效，并转换为0。因此日期和时间类型的转换，要特别注意信息丢失的情况。

1．日期（date）值的转换

（1）转换为datetime或timestamp值会添加00：00：00的时间部分，因为date值不包含时间信息。

（2）转换为time值没有用，结果为00：00：00。

2．日期时间和时间戳值的转换

（1）转换为date值需要考虑秒的小数部分，并对时间部分进行舍入。例如，1999-12-31 23：59：59.499变为1999-12-31，而1999-12-31 23：59：59.500变为2000-01-01。

（2）转换为time值将放弃日期部分，因为time类型不包含日期信息。

3．时间类型的转换

为了将time值转换为其他类型，日期部分使用current_date()的值。time被解释为经过的时间（而不是一天中的时间）并添加到日期中，这意味着如果时间值超出00：00：00到23：59：59的范围，则结果的日期部分与当前日期不同。

例如：假设当前日期为2012-01-01。time值12：00：00、24：00：00和-12：00：00转换为datetime或时间戳值时，分别生成2012-01-01 12：00：00、2012-01-02 00：00：00和2011-12-31 12：00：00。

time到date的转换类似，但丢弃结果中的时间部分，转换结果分别是2012-01-01、2012-01-02和2011-12-31。

将time和datetime值转换为数字形式取决于该值是否包含秒小数部分。当秒小数部分的n为0（或省略）时，time(n)或datetime(n)将转换为整数；当n大于0时，将转换为具有n个十进制数字的十进制值，例如：

提示 curtime()用于获取当前时间，now()用于获取当前日期时间，+0用于将time和datetime值转换为数字形式。

3.2 日期和时间类型