SQL: Data Type: Date and Time

11th October 2021 at 3:36pm

MySQL

📘 官方文档

类型表示
DATE日期
TIME时间
DATETIME日期 + 时间
TIMESTAMP日期 + 时间
YEAR年份

FSP (fractional seconds precision),表示允许保存到几位小数的秒,默认是 0。在建表时指定,如下面的 TIME(3) 即指定了 3 位小数:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

MySQL 如何展示日期类型?

DATE'YYYY-MM-DD'
DATETIME[(fsp)]'YYYY-MM-DD hh:mm:ss[.fraction]'
TIMESTAMP[(fsp)]'YYYY-MM-DD hh:mm:ss[.fraction]'
TIME[(fsp)]'hh:mm:ss[.fraction]'
YEAR[(4)]YYYY

Literals

标准的字面量是:

DATE 'str'
TIME 'str'
TIMESTAMP 'str'

中间的空格可要可不要。

但是实际写 SQL 时很少需要用到:

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-02 10:10:10'), ('2020-01-03 10:10:10');

DATE, DATETIME, TIMESTAMP

类型数值范围
DATE'1000-01-01' to '9999-12-31'
DATETIME'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'

TIMESTAMP 类型无法表示 '1970-01-01 00:00:00',因为这个值对应 epoch 中的 0,被 MySQL 用来表示 '0000-00-00 00:00:00'(zero timestamp)。

当指定了 FSP 后,数值范围也会有小的变化,比如一个 DATETIME 列指定了 3 位小数,那么数据范围变成:'1000-01-01 00:00:00.000' to '9999-12-31 23:59:59.999'。

TIMESTAMP 类型有个特殊逻辑:

  • explicit_defaults_for_timestamp 变量关闭时,当有 INSERT / UPDATE 操作时,表的第一个 TIMESTAMP 列如果没有被设值、或者设了 NULL 但是列定义中不允许 NULL,那么 MySQL 会自动将该列置为当前的时间
  • explicit_defaults_for_timestamp 变量打开时,没有上述自动设置的行为。你仍然可以在列定义中使用 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP 来使其自动更新

TIMESTAMP 比较 鸡肋 的点是:虽然 MySQL 在底层是用 4 字节整形来存储它,但是在设置它的值时,你不能传一个整数给它设置,而是要传日期字符串。然后 MySQL 又根据 time_zone 变量的设定将它转成了 UTC 时间再变成整形值存储下来。这使得 TIMESTAMP 字段无法做到时区无关,也就少了适用面。

当 MySQL 设置中没有关闭 ZERO_DATE 时,日期的值可以是 '0000-00-00' / '0000-00-00 00:00:00'。使用场景:

  • 用来表示类似空字符串的语义
  • 当日期不足够详情时,比如用户设置其生日、只设了年份和月份时,可以用 'YYYY-MM-00' 来表达

Timezone

MySQL 在存储 TIMESTAMP 时,会按服务器时区把日期转成 UTC 存储;读取时再按服务器时区转成 YYYY-MM-DD 来表示。DATETIME 则没有此逻辑;它不带时区信息,不管服务器时区怎样变化,读到的值都是一样的。

MySQL 8.0.19 开始支持在 literal 里面写 time zone offset。没有细看。

最佳实践:采用 Django 的建议,用 DATETIME 存日期,存数据库前先转成 UTC。取出时再转成本地时区展示。Django 的 DateTimeField,在 MySQL 时使用 datetime(6)、在 PG 时使用 timestamp [ (p) ] with time zone

自动初始化及更新日期字段

MySQL 支持自动设置时间字段(支持 DATETIME 及 TIMESTAMP 类型),比如:

CREATE TABLE t1 (
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Django 的 ORM 也提供了类似的能力,但是它要兼容多数据库,应该不是通过这种语法来支持的。因此,最佳实践应该还是在应用代码中手动指定时间字段。