MySQL timestamp的类型与时区实例详解

什么是MySQL timestamp类型？

MySQL中，timestamp类型用来存储日期和时间数据，并且会自动把当前的日期和时间转换为时间戳格式。时间戳是一个以格林威治时间1970年1月1日0时0分0秒（UTC时间）为基准，表示从该时间起到现在的总毫秒数。

MySQL timestamp类型的格式和范围

• 格式：YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示天数，HH、MM、SS分别表示时、分、秒。
• 范围：从1970-01-01 00:00:01到2038-01-19 03:14:07，因为MySQL中timestamp类型占用4字节，能存储的最大数值为2的32次方减1，因此时间戳的最大值为2147483647。

MySQL timestamp类型的时区问题

MySQL中，timestamp类型的默认时区是系统的时区。这意味着，如果你的系统时区是北京时间（UTC+8），那么你每插入一条记录，timestamp类型的值就会自动加上8小时，也就是转换成UTC+8的时间。但是，如果你在不同时区的机器上操作数据库，时间会混乱。因此，需要使用合适的时区函数来解决这个问题。

MySQL timestamp类型的时区函数

MySQL提供了CONVERT_TZ()、TIMESTAMPADD()、TIMESTAMPDIFF()等多个时区函数，这里介绍其中两个常用的函数。

CONVERT_TZ()

CONVERT_TZ()函数用来将一个时区的时间转换为另一个时区的时间。它有三个参数，分别是待转换的时间、当前时区、目标时区。例如，将2022-02-14 08:00:00（UTC+0的时间）转换为北京时间（UTC+8的时间）：

SELECT CONVERT_TZ('2022-02-14 08:00:00', 'UTC', 'Asia/Shanghai');

结果为2022-02-14 16:00:00，可以看到已经转换为北京时间。

FROM_UNIXTIME()

FROM_UNIXTIME()函数可以将时间戳转换为MySQL的日期时间格式。例如，将时间戳1644835200（即2022-02-15 00:00:00）转换为MySQL格式：

SELECT FROM_UNIXTIME(1644835200);

结果为2022-02-15 00:00:00，表示已经成功转换。

示例说明

假设你在美国，机器上的时区为UTC-8，你要在数据库里存储北京时间的记录。

1. 首先，你需要将当前时间转换为北京时间，使用CONVERT_TZ()函数：

SELECT CONVERT_TZ(NOW(), 'America/Los_Angeles', 'Asia/Shanghai');

这里使用了NOW()函数获取当前时间。

1. 然后，将转换后的时间插入到数据库中。

```sql
INSERT INTO test VALUES (1, 'test', CONVERT_TZ(NOW(), 'America/Los_Angeles', 'Asia/Shanghai'));

插入成功后，可以使用`FROM_UNIXTIME()`函数将时间戳转换为MySQL格式：

```sql
SELECT id, name, FROM_UNIXTIME(date) FROM test;

结果为：

+----+------+---------------------+
| id | name | FROM_UNIXTIME(date) |
+----+------+---------------------+
| 1  | test | 2022-02-14 12:00:00 |
+----+------+---------------------+

可以看到，成功地将时间转换为了北京时间。