日期和时间类型

用于表示日期和时间类型的值是 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 类型都是相关的。这里描述它们的共同点和区别。

DATE 用于只有日期部分,没有时间部分。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_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 可以设置这些属性。DEFAULT 也可以指定成某个特定的值,比如 DEFAULT 0 或者 DEFAULT '2000-01-01 00:00:00'

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

时间值中的小数部分

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

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

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

  • 插入一条 TIME,DATETIME 或者 TIMESTAMP 涉及到分数部分时,如果分数位不够,或者过多,可能会涉及到 rounding。例如:
mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO fractest VALUES
     > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM fractest;
+-------------|------------------------|------------------------+
| c1          | c2                     | c3                     |
+-------------|------------------------|------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
+-------------|------------------------|------------------------+
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 也是类似过程,然后丢弃时间部分。

使用 CAST() 函数可以显式地将一个值转换成 DATE 类型,比如:

date_col = CAST(datetime_col AS DATE)

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

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------|-------------|--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------|-------------|--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------|-------------|--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------|----------------|--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------|----------------|--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------|----------------|--------------------+

日期中的两位数字的年

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

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

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

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

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