mysql のタイムスタンプにおけるタイムゾーンの問題の詳細な分析

この記事は、mysql に関する関連知識を提供します。主に、mysql のタイムスタンプのタイム ゾーンの問題について簡単に説明します。この記事では、サンプル コードを通じて詳細に説明しています。誰にとっても非常に役立ちます。この作品には特定の参照と学習価値があります。見てみましょう。皆さんのお役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

はじめに

ご存知のとおり、時間には 2 つのタイプがあります。 mysql では、timestamp と datetime ですが、インターネットで timestamp と datetime の違いを検索すると、インターネット上のタイム ゾーンに関して、完全に反対の結論が多数あることがわかります。主に 2 つのタイプがあります:

timestamp にはタイム ゾーンの問題はありませんが、datetime にはタイム ゾーンの問題があります。その理由は、タイムスタンプが UTC 形式で保存されるのに対し、datetime は時刻文字列の形式で保存されるためです。ブログ投稿の例: datetime と datetime の違いMySQL のタイムスタンプとタイムスタンプの選択にもタイムゾーンの問題があります。ブログ投稿の例: mysql のタイムスタンプのタイムゾーンの問題

この 2 つのビューは混乱を招きます。では、タイムスタンプにタイムゾーンの問題はありますか?

回答: mysql データベースではタイム ゾーンが指定されていないため、デフォルトで米国中部時間
(UTC-06:00) になります。米国では、「3 月 11 日」から「11 月 7 日」。米国中部時間は UTC-05:00 に変更され、UTC 08:00 から 13 時間異なり、冬時間は 14 時間異なります。したがって、保存された時点ですでに「誤差」が生じています。
親愛なる皆さん、タイムスタンプ タイプを使用する場合は、タイム ゾーンの指定に注意する必要があります。データベース構成で指定する場合でも、データベース接続のパラメーター設定で指定する場合でも、タイム ゾーンを指定する必要があります。

serverTimezone=Asia/Shanghai

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

select now();

基本概念

タイム ゾーン:
地理的な制限により、人々は時間認識の違いに適応するためにタイム ゾーンの概念を発明しました。中国のタイムゾーンは East 8 で、 8:00 または GMT 8 で表されますが、日本のタイムゾーンは East 9 で、 9:00 またはと表されます。 GMT 9、中国で午前 8 時であるとき、日本では午前 9 時です。つまり、東 8 地区では 8 時、東 9 地区では 9 時です。これら 2 つの時間は等しいです。
さらに、時間には 2 つの概念があります:

絶対時間:

たとえば、UNIX 時間接尾辞は 1970-01-01 00:00:00 で始まります。 現在までの秒数 (例: 1582416000)。この表現はタイム ゾーンの影響を受けない絶対時間であり、エポックとも呼ばれます。

現地時間:

特定のタイムゾーンを基準にした時間は現地時間です。たとえば、東 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 がタイムスタンプ型、date_time が日時型、create_timestamp と create_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. 上記のように、次の列として定義されます。 type

timestamptime_stamp、create_timestamp手動で挿入するか、now() 関数によって挿入するかに関係なく、東 9 地区の時刻は次のようになります。 East 8 District の時間より 1 時間長いです。これは正しいです。説明 timestamp タイプはタイム ゾーンに関連しています。ただし、date_time と create_datetime## の時間は、 #datetime 型として定義されたフィールドは変更されていません。これは、datetime 型がタイムゾーンに依存しないことを示しています。

結論:

timestamp
にはストレージ内のタイム ゾーンが含まれていますが、datetime にはタイム ゾーンが含まれていません。つまり、インターネット上の最初の記述が正しいことを意味します。 <p><strong>再看个例子</strong><br>我们将东8区的的<code>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 中国語 Web サイトの他の関連記事を参照してください。