MySQL数据类型——时间/日期类型（DATE、TIME、DATETIME、TIMESTAMP、YEAR）

• 上图显示的是MySQL的时态类型，其中CC、YY、MM、DD、hh、mm和ss分别代表世纪、年、月、日、时、分、秒。
• 对于时态值，MySQL提供的类型有：日期和时间（合并或分开表示）、时间戳（一种专门用来记载某个行最近一次修改时间的类型）。
• 此外，在不需要完整日期时，还可以使用专用于表示年的类型。

• MySQL提供了多种存储时态 (与时间相关的)值的类型。在MySQL 5.6版本里，对这些类型进行了多项重要的改进：
  • 对于数据类型TIME、DATETIME和TIMESTAMP，MySQL 5.6.4增加了对小数秒的支持。 这 些类型现在允许的可选小数部分多达6位(微秒)精度。
  • MySQL 5.6.5引入了扩展支持：自动把当前时间戳作为初始值并进行更新。在以前的版本里，这些属性只能用于表里的大部分单个TIMESTAMP列。现在，它们可以用于任何 TIMESTAMP列，并且也可用于DATETIME列。
  • MySQL 5.6.6丢弃了对YEAR(2)的支持，取而代之的是允许创建像YEAR(4)那样的列。
• 下图列出了这些时态数据类型，以及每种类型的合法取值范围。这些范围同时也反映出了在MySQL 5.6.4里对小数秒的额外支持。(对于5.6.4之前的版本，可以将小数秒部分忽略掉。)

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

t1 TIME(3) t2 TIME(6)

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

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

“零”值

• 当为某种日期/时间类型插入非法值时，该类型会把它存储为一个“零”值。
• 下图列出了各种日期/时 间类型的零值情况：

• 如果想要把非法值处理为错误，并拒绝接受，则需要设置相应的SQL模式，更多相关信息请 参考后面"处理无效值"相关文章 。
• “零”值也是那些声明时带有NOT NULL属性的日期/时间类型列的默认值。

• 遵照标准SQL和ISO 8601规范的要求，MySQL的日期表示顺序为“年-月-日”。例如，2015年12月3日将被表示为'2015-12-03'。为满足检索显示要求，可以使用DATE_FORMAT()函数和TIME_FORMAT()函数来显示各种格式的日期和时间 。
• 在输入日期方面，MySQL的处理方式则比较灵活。例如，它可以把两位数的年份转换为四位数的年份 ，并且对于小于10的月和日，不用提供前导数字0。不过 ，这些值的顺序必须为“年 -月-日”。对于平时已习惯性的一些格式，如'12-3-99'或'3-12-99’，解释情况有可能与你想象的样子有所不同。此时 ，可以借助于STR_TO_DATE()函数，把非ISO格式的字符串转换为ISO格式的日期值 。例如，mytbl表拥有一个日期列date_col，你可以像下面那样插入各个值：

INSERT INTO mytbl(date_col) VALUES(STR_TODATE('12-3-99','%m-%d-%Y')); SELECT * FROM mytbl;

• 在下面，会进一步讨论MySQL所使用的日期解释规则。

• DATE、TIME类型分别用于保存日期值和时间值，而DATETIME类型则用于保存日期和时间的组合值。
• 这3种类型值的格式分别是'CCYY-MM-DD'、'hh:mm:ss[.uuuuuu]'和'CCYY-MM-DD hh:mm:ss[.uuuuuu]'，其中的CC、YY、MM、DD、hh、mm、ss和uuuuuu分别代表世纪、年、月、日、时、分、秒和微妙。
• 对于MySQL 5.6.4之前的版本，TIME和DATETIME值也有小数秒部分，只是在存储时它们会被丢弃掉
• 自MySQL 5.6.5起，DATETIME列会自动把当前时间戳作为初始值，并进行更新。更多相关信息在下面会介绍。

类型转换

• 如果把DATE值赋值给DATETIME列，那么MySQL会自动把时间部分补足为'00:00:00'。反方向的转换也同样有效。
• 如果把DATETIME值赋值给DATE或TIME列，那么MySQL会把不相干的部分去掉。
• 例如：

CREATE TABLE t(dt DATETIME, d DATE, t TIME); INSERT INTO t(dt,d,t) VALUES(NOW(),NOW(),NOW()); SELECT * FROM t;

• TIME到DATETIME的转换，依赖于具体的MySQL版本：自MySQL 5.6.4起，当前日期会添加上时间。对于以前的版本，这个转换并不一定会产生有意义的结果。

DATETIME和TIME的时间值区别

• 在MySQL里，DATATIME类型里的时间值与TIME值略有不同。
  • 对于DATATIME类型，时间部分表示的是一天里的时间，且必须是在'23:59:59'的范围内。
  • 但是，TIME值表示的则是一段逝去的时间。
• 这也正是文章开头图片里所示的TIME列的取值范围为何可以包含负值，以及为何可以大于’23:59:59'的具体原因。

• 当往表里插入“短”(不完整)的TIME值时，一定要小心。它们可能不会被解释成你所期望的样子。例如，把'30'和'12:30'插到某个TIME列，那么最终的存储值为'00:00:30'和'12:30:00'。如果想把'12:30'当作“12分30秒”，那么需要以完整的形式(如'00:12:30')来提供它。

• TIMESTAMP是一种时态数据类型，用于储存日期和时间的组合值。(单词timastamp的中文意思是“时 间戳 ”，它的字面意思很容易让人误以为该类型只与时间有关，其实不然)。timestamp数据类型有一些特殊的属性，在下面会介绍。

取值范围

• TIMESTAMP类型列的取值范围是'1970-01-01 00:00:00[.000000]'~'2038-01-19 03:14:07[.999999]'。
• 与DATETIME一样，在MySQL 5.6.4之前，TIMESTAMP值也允许有小数秒部分，但在存储时会被丟弃。
• 它的取值范围与Unix时间密切相关，其中规定1970年的第一天为“零日”，也称作“纪元”。
• 对于每一个TIMESTAMP值，MySQL会用4个字节来把它存储为自纪元以来总共逝去的秒数。1970年的起始确定了TIMESTAMP类型的取值范围下限值。(不过请注意，TIMESTAMP的取值范围并非起始于'1970-01-0100:00:00'。你可能会想当然地认为它就是纪元以来的那个0秒，但0表示的时间戳是'0000-00-00 00:00:00')。取值范围上限值则与4个字节所能表示的最大Unix时间相对应。

时区

• MySQL会按世界标准时间(Universal Coordinated Time，UTC) 来存储TIMESTAMP值。
• 当保存这样 的值时，服务器会把它从会话时区转换为UTC。当以后检索该值时，服务器又会把它从UTC转换回会话时区，从而让你看到与你存储结果一样的时间值。
• 不过，如果另一个客户端使用了另一个时区去连接服务器，并检索该值，那么它所看到的值则是调整为其所设置时区的那个值 。
• 事实上，只要更改一下会话时区设置，就可以在一个会话里看到这样的效果。例如：

CREATE TABLE t(ts TIMESTAMP); SET time_zone = '+00:00'  # 将时区设置为UTC INSERT INTO t VALUES('2000-01-01 00:00:00'); SELECT ts FROM t;

SET time_zone = '+03:00' # 将时区前调3个小时 SELECT ts FROM t;

• 在上面示例里，指定时区所采用的方式为：相对于UTC的小时和分钟的有符号偏移置。在后面"全球化问题"相关文章中我们还会介绍如何像类似于使用'Europe/Zurich'这样的时区名来更改时区设置。

• TIMESTAMP列会自动把当前时间戳作为初始值，并进行更新。此外，如果在定义TIMESTAMP列时 为了允许存放NULL值而带有NULL属性，那么当把NULL存储到该列时，该列值会被设置为当前时间戳。更多相关信息在下面会介绍。

• YEAR是单字节数据类型，其用意在于提高年值的表示效率。
• 在声明YEAR列时，可以指定一个显示宽度M，M值只能为4或2。如果在YEAR列的定义里忽略掉M，那么其默认值将为4。
• YEAR类型的取值范围是1901~2155年。如果只会用到日期里的年份，如出生年份、政府选举年份等，那么使用YEAR就足够了。如果不需要完整的日期值 ，那么用YEAR会比用其他日期类型更省存储空间 。

YEAR(2)已被废弃

• YEAR(2)只显示最后两位数，并且这种类型实际只能存储从1970年到2069年之间的值。
• 如果使用YEAR(2)来存储该范围之外的值，那么最终的显示值将不确定。例如，YEAR(2)列里存储的1970和 2070都会显示为70。
• 避免这类问题的最简单方法是避免使用YEAR(2)，用 YEA(4)来代替。
• 由于这样的存储问题，自MySQL 5.6.6起，就废弃了YEAR(2)：在已有表里，YEAR(2)列会继续保持不变；但对于新表，这样的列会被创建为YEAR(4)。

• MySQL会使用其年值判断规则，将输入的2位YEAR值转换成4位值 (在下面"解释模糊年份值"会介绍)。 例如，97和14会变成1997和2014。不过请注意，当把数值00插到4位的YEAR列时 ，最终存储 的值将会是0000，而不是2000。如果想要把值00转换为2000，那么需要以'0'如或'00'的字符串来指定它。
• TINYINT类型的存储空间占用量与YEAR类型 (只有1个字节)的一样，但取值范围不一样。如果想要用一个整数类型来覆盖YEAR类型所能表示的年份范围 ，那么需要使用SMALLINT(它将占用两倍的空间 )。如果需要表示的年份包含在YEAR类型所能表示的年份范围内，那么与使用SMALLINT相比，使用YEAR更节省空间。

NULL

• 时态列的定义可以包含通用属性NULL或NOT NULL。
• 如果都不指定，则默认为NULL。TIMESTAMP类型除外，其默认值为NOT NULL。

DEFAULT

• 也可以用DEFAULT子句来设定默认值。在另一篇文章中我们介绍了数据类型的默认值。
• 大部分情况下，这些默认值都必须为常量。
• 除了TIMESTAMP(自MySQL 5.6.5起)和DATETIME以外，你都不能使用像CURRENT_TIMESTAMP这样的函数来将DATETIME列的默认值设置为“当前日期和时间”。TIMESTAMP和DATETIME列之所以比较特殊，是因为它们的默认值可以为当前日期和时间。(更多与掌控这些类型默认值的规则相关的信息在下面"时态类型的自动特性"中会介绍)。如果其他类型想要得到这种结果，那么可以在每次创建新行时，显式地将该列的值设置为CURRENT_TIMESTAMP。 另外，也可以使用TIMESTAMP列或DATETIME列来代替，或者设置一个触发器，让它将该列初始化为适当的值（触发器参考https://dongshao.blog.csdn.net/article/details/90486999）。

• 下面描述MySQL如何处理时间值里的小数秒。它主要适用于MySQL 5.6.4及以上的版本。

• 在TIME、DATETIME和 TIMESTAMP类型的声明语法中，允许设置一个可选的小数秒精度(fsp)，精度值最高可达6位数字。
• 值必须是0〜6，0表示没有小数部分，而6则表示精度为微秒。如果没有指定fsp，则默认值为0。
• 例如，TIME和TIME(0)是等价的，都没有小数部分。DATETIME(1)允许日期和时间值精确到十分之一秒。TIMESTAMP(6)允许时间戳的精确值达到微秒级。

• 对于带时态参数的函数，其接受或返回的时态值中都带有小数秒部分。在某些情况下，MySQL 5.6.4之前的版本有些不带参数的函数，现在也开始接受参数，以方便对可能有的返回值进行小数秒位数的控制。例如，CURTIME()返回的是不带小数秒部分的当前时间，而CURTIME(3)返回的时间则包括了一个精度高达千分之一秒的小数秒部分：

SELECT CURTIME(), CURTIME(3);

• 关于各个函数的描述表明了什么时候允许使用fsp参数，请参阅https://dongshao.blog.csdn.net/article/details/88046679
• MySQL 5.6.4之前的版本，只提供了对微秒值的有限支持。有一些时间函数，如DATE_ADD()，使用了这些值，但是如果你想要在某个时间列里存储包含微秒部分的值，那么MySQL会将那个微秒部分丢弃。

• TIMESTAMP和DATETIME列可以有自动初始化属性和自动更新特性。
  • “自动初始化”即意味着，对于新行，如果在INSERT语句里省略了这两种类型的列，那么列会被设置为当前时间戳。
  • “自动更新”即意味着，对于已有的行，当把任何其他列更改为不同值时，这两种类型的列都会被更新为当前时间戳。 (将列设置成它的当前值不算自动更新；这种做法实际上是在防止自动更新)。
• 对于MySQL 5.6.5之前的版本，可以为表里任何单个的TIMESTAMP列指定两种特性中的一种或全部。不能出现一个TIMESTAMP列拥有自动初始化特性，而另一个列拥有自动更新特性的情况 。也不能让多个列拥有自动初始化特性，或者让多个列拥有自动更新特性。
• MySQL 5.6.5扩展并推广了对自动特性的支持：任何TIMESTAMP列都可以有这两种特性中的一种或全部，和DATETIME列一样。
• 还有一个只适用于TIMESTAMP列的特殊特性：如果将列设置为NULL，那么它的值会被设置为当前时 间戳。要想允许TIMESTAMP列存储 NULL值，在定义它时需要带有NULL属性。

语法格式

• 例如，下面是TIMESTAMP的语法格式（DATETIME也是一样的）：

col_name TIMESTAMP [DEFAULT default_value] [ON UPDATE CURRENT_TIMESTAMP]

• 如果同时指定DEFAULT和ON UPDATE属性，它们的顺序无关紧要。
• DEFAULT默认值可以是：
  • CURRENT_TIMESTAMP。
  • 像0那样的常量值，或者是格式'CCYY-MM-DD hh:mm:ss'的值。
  • 也可以使用CURRENT_TIMESTAMP的同义词，如NOW()。
• 自MySQL 5.6.5起，DATETIME列便开始允许这些与DEFAULT和ON UPDATE一样的属性。在5.6.5版本之前，对于DEFAULT属性，DATETIME只允许常量，并且不支持ON UPDATE。
• 如果想让表里的第一个TIMESTAMP列具有一个或两个自动特性，那么可以组合使用DEFAULT属性和ON UPDATE属性来定义它：
  • 如果使用DEFAULT CURRENT_TIMESTAMP，那么列将拥有自动初始化特性。如果指定ON UPDATE CURRENTJTIMESTAMP，那么它还会拥有自动更新特性。
  • 如果两种属性都没有指定，那么MySQL会将列定义为具有DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP两种属性。
  • 如果使用指定了常量值的DEFAULT constant_value属性，那么列将不具有自动初始化特性。如果指定ON UPDATE CURRENT_TIMESTAMP，那么它会有自动更新特性。
  • 如果没有DEFAULT，但有ON UPDATE CURRENT_TIMESTAMP，那么具体的默认 值为0，并且列有自动更新特性。

• 对于MySQL 5.6.5之前的版本，如果要让某个TIMESTAMP列 (而非第一列)使用自动初始化或自动更新特性，那么必须使用DEFAULT constant_value属性显式地定义第一列，同时不带CURRENT_TIMESTAMP属性。然后，对于任何其他的单个TIMESTAMP列，可以使用DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP(或者同时使用这两者)。
• 自MySQL 5.6.5起，对于任何TIMESTAMP列，可以自由地使用这两个属性当中的一个，也可以两个都用。此外，DATETIME列也可以有这些属性。
• 如果不想让拥有自动初始化特性或自动更新特性的TIMESTAMP或DATETIME列 ，受到这些特性的影响 ，那么在执行插入或更新操作时，可以显式地把列设置成所期望的值。例如，为防止在更改列时自动更新，可以将列设置成它的当前值。

NULL

• TIMESTAMP和DATETIME列的定义也可以包含NULL或NOT NULL属性。
• TIMESTAMP的默认属性是NOT NULL。
  • 这会产生一种特殊效果，即当你把列显式地设置成NULL时，MySQL会将它设置成当前时间戳。(对于插入和更新操作，都是如此)。
  • 如果在列的定义里指定NULL，那么将该列设置为NULL时，存储的将是NULL，而不是当前时间戳。
• DATETIME的默认属性是NULL，并且在把DATETIME列设置为NULL时，没有产生特殊效果。

演示案例

• 有如下一张表，包含TIMESTAMP列。在新增行时，列会被设置成当前时间戳，并且之后不会 被自动更新：

CREATE TABLE t1(ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

• 如果要插入新行，可以通过如下几种方式将新行中的ts_create字段设置为当前时间戳：
  • 将列设置为NULL
  • 在INSERT语句中省略ts_create字段
• 下面来看另外一张表：其中包含两个TIMESTAMP类型的列，这两个列分别用于存储创建时间和最周修改时间。

CREATE TABLE t2(  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  ts_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );  INSERT INTO t2;

• 插入一行进行测试：

INSERT INTO t2 VALUES(); SELECT * FROM t2;

• MySQL可以正确解释各种格式的日期和时间列的输入值，其中包括字符串形式和数值形式。 此外，自MySQL5.6.4起，TIME、DATETIME和TIMESTAMP数据类型，还可以带有一个可选的精度高达6位数字(微秒)的小数秒部分。例如，DATETIME和TIMESTAMP值支持的格式有：

'CCYY-MM-DD hh:mm:ss[.uuuuuu[' 'YY-MM-DD hh:mm:ss[.uuuuuu]' 'CCYYMMDDhhmmss[.uuuuuu]' 'YYMMDDhhmmss[.uuuuuu]' CCYYMMDDhhmmss[.uuuuuu] YYMMDDhhmmss[.uuuuuu]

• 对于DATE、TIME和YEAR类型值，也有类似的表示。
• MySQL也可以解释那些没有世纪部分(即CC)的日期和时间格式，所用规则参考上面。

分隔符处理

• 对于带分隔符的字符串格式，日期里可以不用"-"，时间里可以不用":"。
• 任何一种标点符号都可以用作分隔符。对这些值的解释，依据的是其上下文，而非分隔符。例如，虽然人们习惯于把“:”当作时间值的分隔符，但是在期望是日期值的场合，MySQL也不会把一个包含“:”的值解释为具体的时间。
• 此外，对于包含有分隔符的字符串格式，当月、日、时，分或秒的值小于10时，可以不用指定2位数字。例如，下面这些值都是等价的。

'2012-02-03 05:04:09' '2012-02-03 05:04:9' '2012-02-03 05:4:9' '2012-02-03 5:4:9' '2012-02-3 5:4:9' '2012-2-3 5:4:9'

前导零

• 对于带有前导零的日期和时间值，MySQL有多种不同的解释方式，具体取决于这些值是以字符串形式指定的，还是以数字形式指定的。
• 例如，字符串'001231'会被看作是一个有6位数字的值：
  • 如果将其当作DATE类型，那么它会被解释成'2000-12-31'。
  • 而如果将其当作DATETIME类型，那么它会被解释成'2000-12-31 00:00:00'。
• 另外，在解析器将其处理成数字之后，数字001231会被看作1231，于是，对它的解释就会变得不确定。此时，最好是提供一个字符串值'001231'。
• 如果的确需要使用数字，则需要使用完整形式(对于DATE类型，即为20001231；而对于DATETIME类型，即为200012310000)。

• 一般情况下，可以在DATE、DATETIME和TIMESTAMP类型之间随意地相互赋值，但也请记 住，存在以下这样几条限制：
  • 如果把DATETIME或TIMESTAMP值赋给DATE列，则其中的时间部分会被丢失。
  • 如果把DATE值赋值给DATETIME或TIMESTAMP列，则结果值的时间部分将会被设置为零 (即'00:00:00')。
  • 各个类型的取值范围有所不同。尤其是TIMESTAMP类型，其取值范围仅限于1970~2038。因此，不能把一个早于1970年的DATETIME值赋给TIMESTAMP列。如果强 行那样做，最终的结果也不会是你所期望的那样。同样 ，也不能把遥远未来的值赋给 TIMESTAMP列。
• MySQL提供了很多用于处理日期和时间值的函数。更多相关信息请参考https://blog.csdn.net/qq_41453285/article/details/88046679。

• 对于所有那些带有年份值的日期和时间类型(包括DATE、DATETIME、TIMESTAMP和 YEAR)，MySQL在处理那些包含2位数字的年份值时，会把它们转换成4位数字。
  • 年份在00~69之间的值，会被转换为2000〜2069。
  • 年份在70〜99之间的值，会被转换为1970~1999。
• 把各种不同的2位数字值存储到YEAR(4)列，然后再把它们检索出来，便可以看到这些转换规 则的实际效果：

CREATE TABLE y_table (y YEAR(4)); INSERT INTO y_table VALUES(68),(69),(99),(00),('00'); SELECT * FROM y_table;

• 前面的示例也展示了某些应该引起注意的地方：00会被转换为 0000，而不是2000。如果把数 字0插到YEAR(4)列，得到的也将是0000。如果要利用某个没有包含世纪部分的值得到2000年的表示结果，那么必须使用字符串'0'或'00'。如果想确保MySQL看到的是字符串，而非数字，则可以使用CAST(value AS CHAR)函数来插入YEAR值，不管value是字符串，还是数字，其结果都一律是字符串。
• 需要注意的是，把2位数字年份值转换为4位数字年份值的转换规则，只是提供了一种比较合理的猜测。当未指定世纪部分时，MySQL并不知道你的2位数字年份具体指的是哪 一年。 MySQL的年份转换规则适用于很多情形，但如果它们产生的结果与你预期的不一致，那就必须得提供一个无歧义的4位数字年份值。例如，想把自18世纪以来的美国总统的出生日期和去世日期都录到president表里，那么必须使用4位数字的年份值 。这两个列的值跨越了好几个世纪，因此让MySQL根据2位数的年份值去猜测它们属于哪个世纪，绝非明智之举。

• 日期等函数参见文章：https://blog.csdn.net/qq_41453285/article/details/88046679

演示案例

• 查询2005年9月的所有订单

• 当然，我们还可以进行简化

演示案例

• 我们有下面这个查询语句。如果order_date的类型是datetime类型（例如：2005-09-01 11:30:05），那么order_date中还会包含时分秒，下面的查询就会失败

• 因此，我们可以借助date()函数来解决上面那种错误

• 因此，如果查询要的是日期，那么就建议使用date()函数
• 类似的，time()函数等原理都如同上面