MySQL 中 datetime 和 timestamp 类型的比较

·  阅读 218

  DATETIMETIMESTAMP 是 MySQL 中常用的日期/时间数据类型。其中 DATETIME 和 TIMESTAMP 在表象上有相似的地方,但在本质上却有许多不同的地方,如果再涉及到时区(timezone),则又有一些不同的地方。

⒈ 格式

  MySQL 中的日期/时间的格式可以是字符串类型或数值类型,具体取决于上下文。如果上下文中期望的输入是一个日期类型,那么 '2015-07-21''20150721'20150721 都会被解释为 date 类型。

  在标准 SQL 中,日期/时间类型必须由一个类型关键字和一个字符串指定。

DATE 'str' 
TIME 'str' 
TIMESTAMP 'str'
复制代码

  MySQL 继承了标准 SQL 的规范,同时也做了一些扩展。首先,在 MySQL 中不需要指定类型关键字;另外,MySQL 还可以识别对应于标准 SQL 规范的 ODBC 规范。

{ d 'str' }
{ t 'str' }
{ ts 'str' }
复制代码
在 MySQL 中,`TIMESTAMP` 语法最终生成的是一个 `DATETIME` 类型的值,因为 MySQL 中的 `DATETIME` 的范围更接近标准 SQL 中的 `TIMESTAMP` 类型(MySQL 中 `TIMESTAMP` 类型从 1970 年到 2038 年,标准 SQL 中 `TIMESTAMP` 类型从 0001 年到 9999 年)

⓵ MySQL 支持的 DATETIMETIMESTAMP 的格式:

  • 字符串格式 'YYYY-MM-DD hh:mm:ss''YY-MM-DD hh:mm:ss'
    其中,任何标点符号都可以作为日期或时间的分隔符,例如 '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45''2012@12@31 11^30^45' 的结果都是等价的。
    另外,日期和时间中间的分隔符可以是空格,也可以是 T ,如 '2012-12-31 11:30:45''2012-12-31T11:30:45' 的结果也是等价的。

  • 没有分隔符的字符串格式 'YYYYMMDDhhmmss''YYMMDDhhmmss'
    例如 '20070523091528''070523091528' 都会被解释为 '2007-05-23 09:15:28' ,但 '071122129015' 会被解释为 '0000-00-00 00:00:00' ,因为 '90' 不是一个有效的小时数。

  • 数值类型的 YYYYMMDDhhmmssYYMMDDhhmmss

如果格式中年为两位数,则 MySQL 的解析规则如下: 1. 70-99 之间的年会被解释为 1970-1999 2. 00-69 之间的年会被解释为 2000-2069

对于字符串格式,如果月份、日期、小时、分钟、秒的值小于 10,则可以不加前导 0,如 '2015-6-9 1:2:3' 会被解释为 2015-06-09 01:02:03

对于数值格式,长度应该为 6、8、12 或 14 位。如果长度为 8 或 14,则年的长度为 4 位,否则年的长度为 2 位。

⓶ 小数秒

  MySQL 中 DATETIMETIMESTAMP 类型支持小数秒,精度最长为 6 位(微秒)。定义小数秒的语法为 type_name(fsp)

CREATE TABLE t1 (
    t TIMESTAMP(3), 
    dt DATETIME(6) 
);
复制代码

  当向数据表中写入带有小数秒的记录时,如果字段定义的精度小于写入的值的精度,则默认会将小数秒四舍五入到字段定义的精度。如果开启了 TIME_TRUNCATE_FRACTIONAL 模式,则将小数秒截取到字段定义的精度。

CREATE TABLE fractest( 
    c1 TIME(2), 
    c2 DATETIME(2), 
    c3 TIMESTAMP(2) 
);
// 四舍五入
INSERT INTO fractest VALUES('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+
1 row in set (0.00 sec)

// 截取
SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');
INSERT INTO fractest VALUES('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
| 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 |
+-------------+------------------------+------------------------+
2 rows in set (0.00 sec)
复制代码
小数秒和 `DATETIME` 或 `TIMESTAMP` 之间的分隔符只能是 `.`

⒉ 范围

  在 MySQL 中,无论 DATETIME 类型的数据以哪种格式写入,最终 MySQL 解析和展示都是采用 YYYY-MM-DD hh:mm:ss 的格式,其支持的范围为 1000-01-01 00:00:009999-12-31 23:59:59

对于 `DATETIME` 类型,虽然支持的范围从 `1000-01-01 00:00:00` 开始,但早于这个时间的日期时间也可以写入:
CREATE TABLE `time_format_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试'

mysql> insert into time_format_test (ts, dt) values (now(), '0800-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 08:53:29 | 0800-01-01 00:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
复制代码

  MySQL 中 TIMESTAMP 所支持的范围要远小于 DATETIME ,其范围只有 UTC 1970-01-01 00:00:01UTC 2038-01-19 03:14:07

`DATETIME` 和 `TIMESTAMP` 类型都可以包含小数秒,在往这两种数据类型的列中写入包含小数秒的值时,这些小数秒也会被存储。

包含小数秒之后,DATETIME 的范围变为 1000-01-01 00:00:00.000000 9999-12-31 23:59:59.999999 。相应的,TIMESTAMP 的范围变为 UTC 1970-01-01 00:00:01.000000UTC 2038-01-19 03:14:07.999999

⒊ 时区

  MySQL 中,TIMESTAMP 类型的值在保存时会由当前时区转换成 UTC ,在读取时则会从 UTC 转换成指定的时区。而 DATETIME 类型的值在保存和读取时与时区无关,在底层会以 bigint 类型的值存储。默认情况下,MySQL 连接的时区采用的是 MySQL 服务的时区,而 MySQL 服务的时区默认情况下与所在服务器的时区保持一致。如果 MySQL 连接中指定了时区,则当前时区为 MySQL 连接中指定的时区.

CREATE TABLE `time_format_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试'
// 时区默认与服务器时区一致
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
1 row in set (0.00 sec)

mysql> insert into time_format_test (ts, dt) values (now(), now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 10:05:46 | 2021-11-25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
// 修改时区设置,TIMESTAMP 类型的值会随时区变化,DATETIME 类型的值不变
mysql> set time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00      |
+-------------+
1 row in set (0.00 sec)

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 02:05:46 | 2021-11-25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +08:00      |
+-------------+
1 row in set (0.00 sec)

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 10:05:46 | 2021-11-25 10:05:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
复制代码

  TIMESTAMP 类型的值会随着时区变化,与之相关的函数 FROM_UNIXTIME() 的值也会随着时区变化。

mysql> select unix_timestamp('2021-11-25 10:05:46');
+---------------------------------------+
| unix_timestamp('2021-11-25 10:05:46') |
+---------------------------------------+
|                            1637805946 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select from_unixtime(1637805946);
+---------------------------+
| from_unixtime(1637805946) |
+---------------------------+
| 2021-11-25 02:05:46       |
+---------------------------+
1 row in set (0.01 sec)

mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select from_unixtime(1637805946);
+---------------------------+
| from_unixtime(1637805946) |
+---------------------------+
| 2021-11-25 10:05:46       |
+---------------------------+
1 row in set (0.00 sec)
复制代码

  从 MySQL 8.0.19 开始,在写入 DATETIMETIMESTAMP 类型的值时,可以指定时区。指定了时区的日期/时间在写入后会转换成数据库当前设置的时区。此后如果再更改时区设置,TIMESTAMP 类型会随时区变化,但 DATETIME 类型不会随时区变化。

在指定时区时,如果时区小于 10 ,则一定要加前导 0,否则会导致写入的值无效而变为 0 值
// 设置时区时,小于 10 的时区一定要加前导 0
mysql> insert into time_format_test (ts, dt) values ('2021-11-25 10:35:18+09:00', '2021-11-25 10:35:18+01:00');
Query OK, 1 row affected (0.00 sec)
// 设置了时区的值会转换成当前时区的值
mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 09:35:18 | 2021-11-25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 01:35:18 | 2021-11-25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
复制代码

⒋ 自动初始化以及自动更新

  对于 DATETIMETIMESTAMP 类型的列,可以通过设置 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 来对列值进行自动初始化以及自动更新。

  对于设置了自动更新的列,如果对同一行中其他的列进行了更新操作,但值没有发生变化,那么自动更细此时并不会起作用。

mysql> update time_format_test set dt = '2021-11-25 17:35:18' where id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 09:35:18 | 2021-11-25 17:35:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update time_format_test set dt = now() where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from time_format_test;
+----+---------------------+---------------------+
| id | ts                  | dt                  |
+----+---------------------+---------------------+
|  1 | 2021-11-25 11:19:40 | 2021-11-25 11:19:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
复制代码

  对于 TIMESTAMP 类型,如果系统变量 explicit_defaults_for_timestamp 的值为 0,并且列值不允许为 NULL,则在给相应的列赋 NULL 值时,列值会自动变成当前的日期时间值。

CREATE TABLE `time_format_test` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
   `ts` timestamp,
   `dt` datetime,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试';
Query OK, 0 rows affected (0.05 sec)

mysql> select @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> insert into time_format_test (ts, dt) values (null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from time_format_test;
+----+---------------------+------+
| id | ts                  | dt   |
+----+---------------------+------+
|  1 | 2021-11-25 11:27:58 | NULL |
+----+---------------------+------+
1 row in set (0.00 sec)
复制代码

  对于 TIMESTAMP 类型,如果系统变量 explicit_defaults_for_timestamp 的值为 0,并且列值不允许为 NULL ,那么第一个类型为 TIMESTAMP 的列会自动添加 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP

CREATE TABLE `time_format_test` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `ts1` timestamp,
 `ts2` timestamp,
 `dt` datetime,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试';
Query OK, 0 rows affected (0.04 sec)

mysql> show create table time_format_test;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                        |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_format_test | CREATE TABLE `time_format_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试'         |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码

  如果要避免上述这种情况,可以在定义数据表时为 TIMESTAMP 类型的列手动设置默认值或允许其值为 NULL 。除此之外,还可以将系统变量 explicit_defaults_for_timestamp 的值设为 1,这意味着 TIMESTAMP 类型的列的默认值以及自动更新需要明确指定。

  如果 TIMESTAMPDATETIME 类型的列在定义时指定了小数秒的精度,那么自始至终,同一个列的小数秒的精度应该相同。

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
// 以下定义不允许出现
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);
复制代码
分类:
后端
标签: