MySQL 中 datetime 和 timestamp 类

发表于

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

⒈ 格式

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

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

1
2
3
sql复制代码DATE 'str' 
TIME 'str' 
TIMESTAMP 'str'

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

1
2
3
sql复制代码{ d 'str' }
{ t 'str' }
{ ts 'str' }

在 MySQL 中，TIMESTAMP 语法最终生成的是一个 DATETIME 类型的值，因为 MySQL 中的 DATETIME 的范围更接近标准 SQL 中的 TIMESTAMP 类型（MySQL 中 TIMESTAMP 类型从 1970 年到 2038 年，标准 SQL 中 TIMESTAMP 类型从 0001 年到 9999 年）

⓵ MySQL 支持的 DATETIME 和 TIMESTAMP 的格式：

  • 字符串格式 ‘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' 不是一个有效的小时数。
  • 数值类型的 YYYYMMDDhhmmss 或 YYMMDDhhmmss。

如果格式中年为两位数，则 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 中 DATETIME 和 TIMESTAMP 类型支持小数秒，精度最长为 6 位（微秒）。定义小数秒的语法为 type_name(fsp) 。

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
sql复制代码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:00 到 9999-12-31 23:59:59 。

对于 DATETIME 类型，虽然支持的范围从 1000-01-01 00:00:00 开始，但早于这个时间的日期时间也可以写入：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
> sql复制代码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:01 到 UTC 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.000000 到 UTC 2038-01-19 03:14:07.999999 。

⒊ 时区

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
sql复制代码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() 的值也会随着时区变化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
sql复制代码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 开始，在写入 DATETIME 或 TIMESTAMP 类型的值时，可以指定时区。指定了时区的日期/时间在写入后会转换成数据库当前设置的时区。此后如果再更改时区设置，TIMESTAMP 类型会随时区变化，但 DATETIME 类型不会随时区变化。

在指定时区时，如果时区小于 10 ，则一定要加前导 0，否则会导致写入的值无效而变为 0 值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sql复制代码// 设置时区时，小于 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)

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

  对于 DATETIME 和 TIMESTAMP 类型的列，可以通过设置 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 来对列值进行自动初始化以及自动更新。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
sql复制代码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 值时，列值会自动变成当前的日期时间值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
sql复制代码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_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sql复制代码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 类型的列的默认值以及自动更新需要明确指定。

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

1
2
3
4
5
6
7
sql复制代码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)
);

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%