mysql 타임스탬프의 시간대 문제에 대한 심층 분석

이 기사에서는 mysql에 대한 관련 지식을 제공합니다. 주로 mysql의 타임스탬프에 대한 시간대 문제를 소개합니다. 이 기사에서는 모든 사람의 학습이나 작업에 대한 특정 참조가 있는 샘플 코드를 통해 자세히 소개합니다. 그것은 모든 사람에게 도움이 되기를 바랍니다.

추천 학습: mysql 동영상 튜토리얼

소개

우리 모두 알고 있듯이, mysql에는 timestamp와 datetime의 두 가지 시간 유형이 있는데, 인터넷에서 timestamp와 datetime의 차이점을 검색해 보면, 온라인에는 시간대에 대해 완전히 반대되는 두 가지 주요 결론이 있습니다.

timestamp에는 시간대 문제가 없지만 datetime에는 시간대 문제가 있습니다. 그 이유는 타임스탬프가 UTC 형식으로 저장되기 때문입니다. 날짜 시간은 시간 문자열과 유사한 형식으로 저장됩니다. 블로그 게시물 예: MySQL 날짜 시간과 타임스탬프 선택의 차이에도 시간대 문제가 있습니다. 블로그 게시물 예: mysql의 타임스탬프 시간대 문제

두 가지 보기는 다음과 같습니다. 혼란스럽습니다. 그렇다면 타임스탬프에 시간대 문제가 있을까요?

답변: mysql 데이터베이스는 시간대를 지정하지 않기 때문에 기본값은 미국 중부 표준시
(UTC-06:00)입니다. 미국은 "3월 11일"부터 "11월 7일"까지 일광 절약 시간제를 시행합니다. 미국 중부 표준시가 UTC -05:00으로 변경되어 UTC+08:00와 13시간 다르며, 겨울철 UTC+08:00과 14시간 다릅니다. 따라서 저장되는 시점에 이미 "오류"가 있는 것입니다.
타임스탬프 유형을 사용할 때는 누구나 시간대 지정에 주의해야 합니다. 데이터베이스 구성에서 지정하든 데이터베이스 연결의 매개변수 설정에서 지정하든 시간대를 지정해야 합니다.

serverTimezone=Asia/Shanghai

show variables like ‘%time_zone%';
set time_zone='+08:00';

select now();

기본 개념

시간대:
지리적 제한으로 인해 사람들은 시간 인식의 차이에 적응하기 위해 시간대 개념을 고안했습니다. 예를 들어 중국의 시간대는 로 표현되는 동8구입니다. + 8:00 또는 GMT+8, 일본의 시간대는 동부 9이며 +9:00 또는 GMT+9로 표시됩니다. , 중국에서 오전 8시가 되면 일본에서는 오전 9시, 즉 동부 8구에서는 8시, 동부 9구에서는 9시가 됩니다. , 이 두 시간은 동일합니다. +8:00，或GMT+8，而日本的时区是东9区，表示为+9:00，或GMT+9，当中国是早上8点时，日本是早上9点，即东8区的8点与东9区的9点，这两个时间是相等的。
另外时间还有如下两个概念：

绝对时间：

如unix时间缀，是1970-01-01 00:00:00开始到现在的秒数，如：1582416000，这种表示是绝对时间，不受时区影响，也叫纪元时epoch。

本地时间：

相对于某一时区的时间，是本地时间，比如东8区的2020-02-23 08:00:00，是中国人的本地时间，而在此时，日本人的本地时间是2020-02-23 09:00:00，所以本地时间都是与某一时区相关的，脱离时区看本地时间，是没有意义的，因为你并不知道这具体是指的什么时间点。

比如在Java中，Date对象是绝对时间，通过SimpleDateFormat格式化出来的yyyy-MM-dd HH:mm:ss形式的时间字符串，是本地时间，如果SimpleDateFormat没有调用setTimeZone()显示指定时区，那么默认用的是jvm运行在的操作系统上的时区，我们开发机上的时区基本都是GMT+8。

timestamp与datetime区别

如下，我创建了一张表，里面time_stamp是timestamp类型，date_time是datetime类型，create_timestamp、create_datetime是timestamp与datetime类型，但是它们可以由数据库自动生成。

CREATE TABLE `time_test` (
  `id` bigint unsigned,
  `time_stamp` timestamp,
  `date_time` datetime,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
)

1、首先将数据库时区设置为+8:00，即中国的东8区

2、然后如下手动插入一个固定时间的数据，以及用now()函数插入当前时间

3、当插入完数据后，然后我们修改当前会话的时区为+9:00，即日本的东9区，然后再次查看数据

4、如上，定义为timestamp类型的列time_stamp、create_timestamp不管是手动插入的，还是now()函数插入的，东9区都比东8区的时间大1个小时，这是正确的，说明timestamp类型是时区相关的，然而定义为datetime类型的date_time、create_datetime字段，时间都没有变化，这说明datetime类型是时区无关的。

结论：
timestamp또한 시간에는 두 가지 개념이 있습니다.

🎜절대 시간: 🎜🎜예를 들어 유닉스 시간 접미사는 1970-01-01 00:00:00의 초 수입니다. 현재까지 1582416000과 같이 이 표현은 시간대(epoch라고도 함)의 영향을 받지 않는 절대 시간입니다. 🎜🎜현지 시간: 🎜🎜특정 시간대를 기준으로 한 시간은 현지 시간입니다. 예를 들어 East 8 District의 2020-02-23 08:00:00은 중국인의 현지 시간입니다. , 그리고 이때 일본 현지 시간은 2020-02-23 09:00:00이므로 현지 시간은 특정 시간대와 연관되어 있어 현지 시간을 보는 것은 의미가 없습니다. 시간대가 없으면 이것이 어떤 특정 시점을 의미하는지 알 수 없기 때문입니다. 🎜🎜예를 들어 Java에서 Date 객체는 절대 시간입니다. SimpleDateFormat 형식의 시간 문자열은 yyyy-MM-dd HH:mm 형식입니다. ss. 현지 시간. SimpleDateFormat이 지정된 시간대를 표시하기 위해 setTimeZone()을 호출하지 않는 경우 기본 시간대는 jvm이 있는 운영 체제의 시간대입니다. 우리 개발 컴퓨터의 시간대는 기본적으로 GMT+8입니다. 🎜🎜timestamp와 datetime의 차이점🎜🎜다음과 같이 time_stamp가 timestamp 유형, date_time이 datetime 유형, create_timestamp와 create_datetime이 timestamp와 datetime 유형인 테이블을 생성했는데 데이터베이스에서 자동으로 생성할 수 있습니다. 🎜

$ "date" --date="2020-02-23 08:00:00 +08:00" +%s
1582416000

🎜1. 먼저 데이터베이스 시간대를 중국 동부 8구인 +8:00으로 설정하세요🎜

🎜🎜2. 그런 다음 고정 시간 데이터를 다음과 같이 수동으로 삽입하고 now() 함수를 사용하여 현재 시간🎜

🎜🎜3. 데이터를 삽입한 후 현재 세션의 시간대를 일본 동부 9구인 +9:00로 수정한 후 다시 데이터를 봅니다. 🎜

🎜🎜4. 위와 같이 timestamp로 정의됩니다. time_stamp 및 create_timestamp 유형의 열을 수동으로 삽입할지 지금 삽입할지 여부 () 함수는 East 9 District가 더 좋습니다. East 8 District의 시간이 1시간 더 높다는 뜻입니다. 이는 timestamp 유형이 시간대와 관련되어 있음을 의미합니다. datetime 유형의 date_time으로 정의되어 있습니다. >, create_datetime 필드에는 시간이 변경되지 않았으며 이는 datetime을 나타냅니다. code> 유형은 시간대와 무관합니다. 🎜🎜🎜결론: 🎜🎜timestamp에는 저장소의 시간대가 포함되어 있지만 datetime에는 시간대가 포함되어 있지 않아 인터넷의 첫 번째 진술이 정확함을 보여줍니다. 🎜

再看个例子
我们将东8区的的2020-02-23 08:00:00转换为unix时间缀（绝对时间），再插入数据库试试？
如下，使用linux的date命令转换时间串为unix时间缀：

$ "date" --date="2020-02-23 08:00:00 +08:00" +%s
1582416000

然后用mysql的()函数，将unix时间缀转换为mysql时间类型来插入数据。

如上，查询出来的时间，也是东9区的9点，时间也是正确的。

为什么网上又说timestamp类型存在时区问题？

我发现网上说timestamp有时区问题，都是应用端插入数据，然后到数据库中去看，结果发现时间不一样，因此我打算在Java中写个Demo试一下，看能不能重现这个问题。

1、首先，下面是Java中Entity的定义，与上面的time_test表对应，注意，这里面时间属性都是用Date类型定义的，如下：

2、然后，我写了两个接口/insert与/queryAll来插入与查询数据，如下：

3、然后我把数据库的时区设置为+09:00时区，即日本的东9区，如下：

4、然后调用/insert接口插入数据，注意我接口传入的时间是东8区的8点，如下：

5、插入完后，去数据库中查询一把，如下：

可以看到，time_stamp字段时间是9点，且我已将数据库时区设置为东9区，东9区的9点与东8区的8点，这两个时间实际是相等的，因此时间数据没错。

6、然后我使用/queryAll接口将数据查询出来，如下：

timeStamp属性是1582416000000，这是毫秒级的时间缀，秒级则是1582416000，对应是东8区的2020-02-23 08:00:00，时间数据也没错！

7、然后我又将mysql时区修改回+8:00，并重启我们的java应用，如下：

8、再查询一下数据，如下：

timeStamp属性还是1582416000000，时间没有变化，这也是正确的。

那为什么网上会说timestamp存在时区问题？

经过一翻查看，我发现他们都提到了jdbc的serverTimezone，会不会是这个配置错误导致的呢？就先试试吧！
1、如图，我把数据库时区修改回+9:00时区，然后故意把jdbc的url上的serverTimezone配置为与数据库不一致的GMT+8时区，然后重启java应用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

其中GMT%2B8就是GMT+8，因为在url上需要urlencode，所以就变成了GMT%2B8。

2、重新插入数据，注意插入的时间还是东8区的8点，如下：

3、然后，我再到数据库中查询一把，如下：

time_stamp中时间竟然是8点！要知道我们虽然插入的是东8区的8点，但当前会话可是东9区的，东8区的8点等于东9区的9点，所以正确显示应该为9点才对，时间差了1小时！

4、然后，我又调用/queryAll接口查询了一把，想看看mybatis查询出来的时间数据对不对，如下：

可以看到timeStamp是1582416000000，秒级是1582416000，这个时间就是东8区的8点，东9区的9点啊！查询出来的时间竟然是正确的，为什么？？？

serverTimezone的本质

为了找出问题所在，我调试了一下mysql的jdbc驱动代码，终于弄明白了原因，主要可以看看如下这几点：

1.mysql驱动创建连接后，会调用com.mysql.jdbc.ConnectionImpl#configureTimezone()来配置此连接的时区，如果配置了serverTimezone，则会使用serverTimezone配置的时区，没配置时会去取数据库中的time_zone变量，这就是为什么我们没有配置serverTimezone变量时，结果也是正确的。

//若使用普通驱动，使用此方法配置mysql连接的时区
com.mysql.jdbc.ConnectionImpl#configureTimezone()
//若使用cj驱动，使用此方法配置mysql连接的时区
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()

2.调用jdbc的setTimestamp()方法时，实际调用的是com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()，这里面会根据serverTimezone指定的时区，将对应的Timestamp对象转换为serverTimezone指定时区的本地时间字符串。

3.执行sql语句时，会执行com.mysql.cj.jdbc.ClientPreparedStatement#execute()，这里面sendPacket变量保存着真实会发送到mysql的sql语句。

注：看的是8.0.11版本mysql-connector-java驱动源码，不同版本代码会稍有差异，比如5.2.16版本驱动，jdbc url上需要同时配置这两个配置：useTimezone=true&serverTimezone=GMT%2B8，且setTimestamp()对应的是com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。

原理总结如下：
mysql驱动在发送sql前，会将jdbc中的Date对象参数，根据serverTimeZone配置的时区转化为日期字符串后，再发送sql请求给mysql server，同样在mysql server返回查询结果后，结果中的日期值也是日期字符串，mysql驱动会根据serverTimeZone配置的时区，将日期字符串转化为Date对象。

因此，当serverTimeZone与数据库实际时区不一致时，会发生时区转换错误，导致时间偏差，如下：
a、比如sql参数是一个Date对象，时间值是东8区的2020-02-23 08:00:00，注意它里面存储的可不是2020-02-23 08:00:00这个字符串，它是Date对象(绝对时间)，只是我用文字表达出来是东8区的2020-02-23 08:00:00。
b、然后，由于serverTimeZone配置的是东8区，mysql驱动会将这个Date对象转为2020-02-23 08:00:00，注意这时已经是字符串了，然后再将sql发送给mysql，注意这里的sql里面已经将Date参数替换为2020-02-23 08:00:00了，因为Date对象本身是无法走网络的。
c、然后mysql数据库接收到这个时间字符串2020-02-23 08:00:00后，由于数据库时区配置是东9区，它会认为这个时间是东9区的，它会以东9区解析这个时间字符串，这时数据库保存的时间是东9区的2020-02-23 08:00:00，也就是东8区的2020-02-23 07:00:00，保存的时间就偏差了1个小时。
d、查询结果里时间为什么又对了呢，因为查询结果返回了东9区的时间字符串，而java应用又将其理解为是东8区的时间，负负得正了！

将serverTimezone与mysql时区保持一致

so，那么如果我们将serverTimezone配置改正确，即与数据库保持一致时，应该查询到的时间就会是错的，会少1个小时。

1、jdbc url中使用与数据库一样的东9区GMT+9，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

其中的GMT%2B9，即是GMT+9。

2、然后重启Java应用，再查询一把看看，如下：

返回的是毫秒级时间缀1582412400000，秒级就是1582412400，使用linux的date命令转换为时间字符串形式：

$ "date" --date="@1582412400" +"%F %T %z"
2020-02-23 07:00:00 +0800

看到没，它是东8区的7点，刚好差了1个小时。

3、所以，使用mysql的timestamp类型时，对于java应用来说，一定要保证jdbc url中的serverTimezone与数据库中的时区配置是一致的。
另外一点是，当没有配置serverTimezone时，mysql驱动会自动读取mysql server中配置的时区，这里面也有坑！如下：

mysql驱动自动读取数据库时区的坑

3.1 mysql安装好后，默认时区是SYSTEM，而SYSTEM指的是system_time_zone变量的时区，如下：

3.2 当mysql驱动读到time_zone变量是SYSTEM时，会再去读取system_time_zone变量，而system_time_zone对于国内来说，默认是CST，这是一个混乱的时区，是4个不同时区的缩写，如下：

对于Linux或MySQL，会认为CST是中国标准时间(+8:00)，但Java却认为CST是美国标准时间(-6:00)(注：可能和Java运行在Windows中有关)：
如下，linux中CST等于+0800，即中国时区：

$ "date" +"%F %T %Z %z"
2021-09-12 18:35:49 CST +0800

如下，java中CST等于-06:00，美国时区：

3.3 因此mysql驱动取到CST这个时区值时，它会以为这是-6:00时区，但MySQL却理解为+8:00时区，因此MySQL时区一定不要配置为CST，而要配置为具体的时区，如+8:00，但如果MySQL时区为CST且不可修改的情况下，一定要配置jdbc的serverTimezone为清晰的时区(如：GMT+8)。

Entity中日期属性是String呢？

1、我们将Entity对象中的时间属性改为String（不推荐），如下：

2、然后也写两个接口，/insert2与/queryAll2，如下：

3、然后插入数据，注意这时我是直接将无时区的8点，作为参数给到sql的，如下：

4、然后再查询一把，如下：

如上所示，time_stamp字段值是8点，但此时数据库时区是东9区，所以这是东9区的8点。
5、然后我将数据库与jdbc中serverTimezone都改为东8区呢，改完后重启Java应用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

6、再次插入数据，参数还是无时区的8点，如下：

7、再查询一把，如下：

如上所示，time_stamp字段值是8点，但现在数据库时间是东8区，所以这是东8区的8点。
8、然后我再将jdbc url上的serverTimezone调整为东9区，然后重启Java应用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

现在serverTimezone与数据库中不一致，数据库是东8区，serverTimezone是东9区。
9、我们再次插入无时区的8点，如下：

10、然后再查询一把，如下：

time_stamp字段值还是8点，数据库是东8区，所以这是东8区的8点，但我们serverTimezone与数据库的时区不一致啊，没看到时间有偏差，为什么？

解释一下
前面说过了，对于jdbc中的Date对象，在发送给mysql前，会先根据serverTimezone转换为相应时区的时间字符串，但现在Entity中时间属性是String类型，mysql驱动不会进行转换，所以不管serverTimezone怎么配置，对String类型的时间串都没影响。

这样的话，似乎java中日期类型用时间字符串来存还好些，不容易出错，但请再认真考虑一下，调用方传了一个无时区的8点，数据库自作主张，就将其认为是东9区的8点，但如果这个时间字符串实际是东8区的8点呢？这时如果保存到数据库中为东9区的8点，那数据就存错了！

那如果目前api接口就传的无时区的时间串，Entity中就定义的String，怎么解决呢？
1、询问接口定义人员，这个接口的时间串指的是哪个时区的，比如是东8区的2020-02-23 08:00:00。
2、然后接口接收到时间后，要以东8区将时间字符串转换为Date对象，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
Date date = sdf.parse("2020-02-23 08:00:00");

3、然后如果Entity中时间属性定义的是String，那么我们要再将Date对象以数据库的时区格式化为对应的时间字符串，比如数据库时区是东9区，那么格式化后就是2020-02-23 09:00:00，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+9"));
String dateStr = sdf.format(date);
entity.setTimeStamp(dateStr);

4、然后将Entity保存到mysql中的，就也会是东9区的2020-02-23 09:00:00，结果正确。

所以，使用String类型来存储时间数据，要想将时间值保存正确，超级麻烦，不建议在实际开发中这种使用。

最佳实践

1、大多数团队会规定api中传递时间要用unix时间缀，因为如果你传一个2020-02-23 08:00:00时间值，它到底是哪个时区的8点呢？对于unix时间缀，就不会有此问题，因为它是绝对时间。而如果某些特殊原因，一定要使用时间字符串，最好使用ISO8601规范那种带时区的时间串，比如：2020-02-23T08:00:00+08:00。

2、Mybatis中Entity定义要与数据库定义一致，数据库中是timestamp，那么Entity中要定义为Date对象，因为mysql驱动在执行sql时，会自动根据serverTimezone配置帮你转换为数据库时区的时间串，如果你自己来转换，你极有可能因为忘记调用setTimeZone()方法，而使用当前java应用所在机器的默认时区，一旦java应用所在机器的时区与数据库的时区不一致，就会出现时区问题。

3、jdbc的serverTimezone参数，要配置正确，当不配置时，mysql驱动会自动读取mysql server的时区，此时一定要将mysql server的时区指定为清晰的时区(如：+08:00)，切勿使用CST。

4、如果数据库时区修改后，jdbc的serverTimezone也要跟着修改，并重启Java应用，就算没有配置serverTimezone，也需要重启，因为mysql驱动初始化连接时，会将当前数据库时区缓存到一个java变量中，不重启Java应用它不会变。

数据库中用timestamp还是int来存储时间？

如果用int型时间缀存储，不管数据库时区是啥，都不影响，因为存储的是绝对时间，看起来完美解决了时区问题。
但从某些角度看，这种方案只是把时区问题从数据库端推到应用端去了，时区问题将出现在将时间字符串转换为时间缀的过程中，比如某程序员从api接口中拿到时间字符串后，没考虑时区，直接转为unix时间缀，就可能出现时区问题。
因此，对于不带时区的时间串解析，一定要问清楚这是哪个时区的时间，并在代码中显式指定！

另外，用int存储时间还有如下3个不好的点：

开发人员看到这个字段后，无法一目了然的了解到这个时间缀大概是个什么时间，需要去转换一下，会很繁琐。像update_time这样的字段，数据库提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的机制，这样在更新任何字段时，update_time会自动更新，而如果使用int存储，就需要程序员每次更新表时，重新set这个字段，容易遗忘。由于int只有4个字节，用它来存储时间，会在2038年后溢出，而对于timestamp来说，MySQL将其底层存储统一修改为8个字节，相对来说还是比较容易的。

当然，也并不是建议不用int，这是见仁见智的，不管用timestamp还是int，都没有致命性问题的。

总结

timestamp本身是没有时区问题的，时区问题是由于serverTimezone配置错误、mysql使用CST这种混乱时区或Entity中将日期定义String类型导致的。

推荐学习：mysql视频教程

위 내용은 mysql 타임스탬프의 시간대 문제에 대한 심층 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!