mysql 关于时间类型的刨坑之路

问题背景

前两天有做一个基于binglog的数据库实时同步,一张老数据表里有DATETIME、TIMESTAMP不同的时间字段类型,看起来值都是一样的,并且默认值都设置的 0000-00-00 00:00:00,导致我这边读取binlog更新数据库直接悲剧。

由于本人之前一直习惯使用int存储时间戳(问题相对比较少,容易处理),所以对这几个时间字段类型了解不是很多.

后来参考了今天分享的这篇文档,终于有了一个基本的认识。

中文方面优秀的文档有很多,为什么还要发这篇骚扰别人.

因为本文的翻译者水平比较差, 翻译的初衷也是想顺便提升下英语能力,导致下面的有很多地方读起来可能并不是很通顺,但过分加工怕会是错误的描述.所以如果有误导读者地方,本文概不负责

以下是官方文档原文翻译

The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

DATE、DATETIME 和 TIMESTAMP这三个字段是相关联的。咱们这个章节就是来介绍一下它们的特点，它们有哪些相似之处，又有哪些不同之处。MySQL认证了DATE, DATETIME 和 TIMESTAMP 它们的值的有效格式，具体的描述在9.1.3章节中介绍（Date and Time Literals）。“supported” means that although earlier values might work, there is no guarantee.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

DATE 这个类型的值是由日期组成的且没有时分秒等。 在MySQL中DATE检索和显示的格式是'YYYY-MM-DD'（年月日例如2018-11-17）.DATE的支持的取值范围是'1000-01-01' to '9999-12-31'

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

DATETIME 这个类型的值是由日期和时分秒组成的。在MySQL中DATETIME检索和显示的格式是'YYYY-MM-DD HH:MM:SS''（年月日例如2018-11-17 14:30:45）.DATETIME的支持的取值范围是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIMESTAMP 这个类型的值是由日期和时分秒组成的。TIMESTAMP的支持的取值范围是'1970-01-01 00:00:01' to '2038-01-19 03:14:07'。这个就比较确切了，使用有风险，使用需谨慎。

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

一个DATETIME 或者 TIMESTAMP 类型的值会在以微妙（6位）作为精确度时会引入极小的延迟。需要特别注意的是，不管多小的数值只要是以DATETIME or 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' to '2038-01-19 03:14:07.999999'.。这些小数部分是用小数点分开的，不是别的公认的小数分隔符。有关微秒数的信息，看11.3.6章节的内容（“Fractional Seconds in Time Values”）可以看到。

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

TIMESTAMP 和 DATETIME 类型提供了自动初始化和更新当前的日期和时间。更多详情将参考11.3.5章节中查看（“Automatic Initialization and Updating for TIMESTAMP and DATETIME”）

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone Support”.

MySQL将 TIMESTAMP 的值从当前时区转换成UTC进行存储，并且会从UTC转成当前时区进行检索（这样就不会发生出现别的类型例如DATETIME）。默认情况下，每个连接的当前时区是服务器的时间。在每个基础连接中时区都可以被设置。由于时区设置能保持不变，你也就能拿到你存储的数值。如果你存储了TIMESTAMP的值，然后更改了时区并且从数据库取回这个值，这个被取回的值跟你存储的值是不一样的。这个发生的原因是用的不是相同的时区用了相同的转变的方式。当前时区是可以根据当前时区的系统变量获取的。想要了解更多信息请查看5.1.13章节的信息（“MySQL Server Time Zone Support”）

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

无效的DATE, DATETIME, or TIMESTAMP 值会被修改为0 即合适的类型（('0000-00-00' or '0000-00-00 00:00:00')）

MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the :, but is interpreted as the year '2010-11-12' if used in a date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a valid month.

MySQL 对指定字符串的值的格式要求比较宽松，在date部分和时间部份可以使用任何标点符号作为定界符。在一些事例中，这样的语法可以欺骗你。例如：'10:11:12'这个值因为：看起来像时间的值，但是如果它被当日期内容就会被理解成'2010-11-12'。 数值'10:45:15' 会被修改为'0000-00-00' 因为45不是月份的值。

The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

在date和time的微妙数 唯一被公认的定界符是小数点。

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'.

MySQL 不接受 TIMESTAMP的值（在天或者月份里只有一个0，在MySQL中这是无效的日期）。这条规则唯一的例外是0它被理解为'0000-00-00 00:00:00'。

Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:

日期里面用两位数表示年的值是模糊的因为不知道是哪个世纪。

MySQL 对两位数表示年的规定了以下规则：

Year values in the range 00-69 are converted to 2000-2069.

范围在00-69年的被修改为2000-2069

Year values in the range 70-99 are converted to 1970-1999.

范围在70-99年的被修改为1970-1999.

－－－－还没有完－－－－

问题总结:

1. 无效的值和默认设置0000-00-00 00:00:00,显示值插入会报错。

2.底层存储时间都是UTC,然后操作时候根据时区环境变量转换成相应时区。