日期和时间类型

用于表示日期和时间类型的值是 DATE，TIME，DATETIME，TIMESTAMP 和 YEAR。每一种类型都有自己的有效值的范围，也有一个零值用于表示它是一个无效的值。TIMESTAMP 类型有个自动更新的行为，后面介绍。

处理日期和时间类型时，请记住下面这些：

  • 尽管 TiDB 尝试解释不同的格式，日期部分必须是按 年-月-日 的顺序（比如，’98-09-04’），而不是 月-日-年 或者 日-月-年 的顺序。
  • 日期值中包含两位数字的年份是有歧义的，TiDB 按下面规则解释：
    • 范围在 70-99 之间的被转换成 1970-1999
    • 范围在 00-69 之间的被转换成 2000-2069
  • 如果上下文里面需要的是一个数值，TiDB 自动将日期或时间值转换成数值类型，反之亦然。
  • 如果 TiDB 遇到一个日期或时间值是超过表示范围的，或者无效的，会自动将它转换为该类型的零值。
  • 设置不同的 SQL mode 可以改变 TiDB 的行为。
  • TiDB 允许 DATE 和 DATETIME 列中出现月份或者日为零的值，比如 ‘2009-00-00’ 或 ‘2009-01-00’。如果这种日期参与计算，比如函数 DATE_SUB() 或者 DATE_ADD()，得到的结果可能会不正确。
  • TiDB 允许存储零值 ‘0000-00-00’，有时候这会比 NULL 值更方便一些。

下面的表格里面显示了不同类型的零值：

Date Type “Zero” Value
DATE ‘0000-00-00’
TIME ‘00:00:00’
DATETIME ‘0000-00-00 00:00:00’
TIMESTAMP ‘0000-00-00 00:00:00’
YEAR 0000

DATE，DATETIME 和 TIMESTAMP 类型

DATE，DATETIME，TIMESTAMP 类型都是相关的。这里描述它们的共同点和区别。

DATA 用于只有日期部分，没有时间部分。TiDB 按照 ‘YYYY-MM-DD’ 格式接受和显示 DATE 类型的值。支持的值的范围是在 ‘1000-01-01’ 到 ‘9999-12-31’。

DATETIME 包含了日期和时间部分，格式是 ‘YYYY-MM-DD HH:MM:SS’。支持的值的范围是在 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。

TIMESTAMP 包含了日期和时间部分，值的范围是UTC时间 ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’。

DATETIME 和 TIMESTAMP 值可以包含一个最多6位的分数部分，精确到毫秒精度。任何 DATETIME 或 TIMESTAMP 类型的列里面，分数部分都会被存储下来，而不是丢弃。如果包含分数部分，那么值的格式就是 ‘YYYY-MM-DD HH:MM:SS[.fraction]’，分数的范围是 000000-999999。分数和其它部分之间必需用小数点分隔。

TiDB 将 TIMESTAMP 从当前时区转成 UTC 时区存储，检索时再从 UTC 时区转化到当前时区（注意，DATETIME 并不会这样处理）。每个连接默认的时区是服务器的本地时区，可以通过 time_zone 环境变量进行修改。只要时区保持不变，存储和取回来的值都是一样的。如果存储的是 TIMESTAMP 值，并且时区改变了，那么存储的值和读出来的值会发生变化。

不合法的 DATE，DATETIME，TIMESTAMP 值会被自动地转成相应类型的零值（’0000-00-00’ 或 ‘0000-00-00 00:00:00’）。

注意，TIMESTAMP 类型的值是不允许月份或者日里面出现零的，唯一的例外是零值本身 ‘0000-00-00 00:00:00’。

两位数的年份是有歧义的，会按照如下规则解释：

  • 00-69 范围被转换成 2000-2069
  • 70-99 范围被转换成 1970-1999

TIME 类型

TIME 类型的值的格式是 ‘HH:MM:SS’，值的范围是 ‘-838:59:59’ 到 ‘838:59:59’。时间部分比较大，是因为 TIME 类型不仅用于表示一天里面的时间，也可以用于两个事件之间的时间间隔。

TIME 类型可以包含分数部分，如果包含分数部分，那么 TIME 的表示范围则是 ‘-838:59:59.000000’ 到 ‘838:59:59.000000’。

注意缩写的时间，’11:12’ 表示的是 ‘11:12:00’ 而不是 ‘00:11:12’，然而 ‘1112’ 表示的是 ‘00:11:12’。这里的区别是是否包含分号 :，处理起来是不一样的。

YEAR 类型

YEAR 的值的格式是 YYYY，表示范围从 1901 到 2155，或者是零值 0000。

指定 YEAR 的值可以按下列格式：

  • 4位数字从 1901 到 2155
  • 4位字符串从 ‘1901’ 到 ‘2155’
  • 1位或者2位数字，从 1 到 99。相应的，1-69 会被转换为 2001-2069，70-99 会被转换为 1970-1999
  • 1位或者2位字符串，从 ‘0’ 到 ‘99’
  • 数值的 0 会被当作 0000，而字符串的 ‘0’ 或 ‘00’ 会被当作 2000

不合法的的 YEAR 的值被会自动转换成 0000。

TIMESTAMP 和 DATETIME 的自动初始化和更新

TIMESTAMP 和 DATETIME 列可以被自动初始化或者更新为当前时间。

对于表里面任意的 TIMESTAMP 或者 DATETIME 列，可以将默认值或者自动更新值指定为 current timestamp。

通过在列定义时指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 可以设置这些属性。DEFAULT 也可以指定成某个特定的值，比如 DEFAULT 0 或者 DEFAULT '2000-01-01 00:00:00'。

  1. CREATE TABLE t1 (
  2.   ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  3.   dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  4. );

除非指定了 NOT NULL，否则 DATETIME 的默认值是 NULL，如果 NOT NULL 时不指定默认值，默认值是 0。

  1. CREATE TABLE t1 (
  2.   dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  3.   dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
  4. );

时间值中的小数部分

TIME，DATETIME，TIMESTAMP 支持分数部分，可以精确到毫秒精度。

  • 使用 type_name(fsp) 来定义支持分数精度的列，其中 type_name 可以是 TIME，DATETIME 或者 TIMESTAMP，例如：
  1. CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp 必须是从 0 到 6。0 表示没有分数部分，如果 fsp 忽略，则默认就是 0。

  • 插入一条 TIME，DATETIME 或者 TIMESTAMP 涉及到分数部分时，如果分数位不够，或者过多，可能会涉及到 rounding。例如：
  1. mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
  2. Query OK, 0 rows affected (0.33 sec)
  4. mysql> INSERT INTO fractest VALUES
  5.      > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
  6. Query OK, 1 row affected (0.03 sec)
  8. mysql> SELECT * FROM fractest;
  9. +-------------|------------------------|------------------------+
  10. | c1          | c2                     | c3                     |
  11. +-------------|------------------------|------------------------+
  12. | 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
  13. +-------------|------------------------|------------------------+
  14. 1 row in set (0.00 sec)

日期和时间类型转换

有时候可能会需要在日期类型间进行转换，某些转换可能会丢失信息。例如，DATE，DATETIME，TIMESTAMP 的值都是有各自的表示范围的。TIMESTAMP 不可以早于 UTC 时间的 1970 年，或者晚于 UTC 时间的 ‘2038-01-19 03:14:07’，这意味着 ‘1968-01-01’ 是一个合理的 DATE 或者 DATETIME 日期值，但是转换成 TIMESTAMP 时会变成 0。

DATE 的转换：

  • 转成 DATETIME 或者 TIMESTAMP 会添加时间部分 ‘00:00:00’，因为 DATE 不包含时间信息
  • 转成 TIME 会变成 ‘00:00:00’

DATETIME 和 TIMESTAMP 的转换：

  • 转成 DATE 会丢弃时间和分数部分，比如 ‘1999-12-31 23:59:59.499’ 变成 ‘1999-12-31’
  • 转成 TIME 会丢弃日期部分，因为 TIME 里面不包含日期信息

将 TIME 转成其它时间日期格式时， 自动地使用 CURRENT_DATE() 作为日期部分，最后生成的是 TIME 的时间加上 CURRENT_DATE() 之后得到的日期，也就是说如果 TIME 的值不是在 ‘00:00:00’ 到 ‘23:59:59’ 范围内，转换之后的日期部分并不是当天。

TIME 转成 DATE 也是类似过程，然后丢弃时间部分。

使用 CASE() 函数可以显示的转换类型，比如：

  1. date_col = CAST(datetime_col AS DATE)

将 TIME 和 DATETIME 转换成数值格式：

  1. mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
  2. +-----------|-------------|--------------+
  3. | CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
  4. +-----------|-------------|--------------+
  5. | 09:28:00  |       92800 |    92800.887 |
  6. +-----------|-------------|--------------+
  7. mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
  8. +---------------------|----------------|--------------------+
  9. | NOW()               | NOW()+0        | NOW(3)+0           |
  10. +---------------------|----------------|--------------------+
  11. | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
  12. +---------------------|----------------|--------------------+

日期中的两位数字的年

日期中包含两位数字的年是带有歧义的，因为不知道世纪。

对于 DATETIME，DATE 和 TIMESTAMP 类型，TiDB 采用如下规则处理歧义：

  • 范围在 00-69 被转换成 2000-2069
  • 范围在 70-99 被转换成 1970-1999

对于 YEAR 也是类似过程，只有一个例外，数字的 00 插入到 YEAR(4) 会变成 0000 而不是 2000。如果想解释成 2000，则必需指定成 2000 或者是 ‘0’ 或 ‘00’。

有些函数比如 MIN() 和 MAX() 对于两位数字的年处理可能不太好，使用四位格式会比较合适。