21時間ズレた日時が登録される謎にせまる

はじめに

自社開発のデータ取込みツールで、社内サーバーでは正しい登録日時がセットされたものの、検証サーバーでは21時間前の日時が登録されていました。
IISログなどでは9時間前の日時が出力されるので9時間ズレなら分かるのですが、21時間ズレはどういうことなのか？

現象

データ取込みツールでは、CSVファイルから2つの対象テーブルに登録します。
各テーブルには登録日および登録時刻がセットされています。
検証サーバーでデータ取込みツールを実行したので、データ登録されているかSQLを使用して登録日で抽出して確認すると、最初のテーブルはデータが抽出されてきたのですが、もう一方のテーブルはデータが抽出されませんでした。
登録に失敗したのかと思い、データ取込みツールのログを見てもエラーは出力されていません。それではと登録日の最大値で抽出してみると昨日の日付が返ってきました。そのテーブルを昨日の日付で抽出してみると登録日時には21時間前の日時がセットされていました。

調査

自PC(クライアント)でデータベース操作ツール「A5:SQL Mk-2」を使用しています。

1.検証サーバーPCの日時を確認、正しかったです。
2.接続先：検証サーバーでSELECT now()を実行すると正しい日時が返りました。
3.接続先：検証サーバーで、show timezoneを実行すると「-9:00」が返りました。
4.接続先：社内サーバーで、show timezoneを実行すると「Asia/Tokyo」が返りました。
5.検証サーバーPCのpostgre.confのtimezone = 'Etc/GMT+12'がセットされていました。
6.検証サーバーPCの「pgAdmin 4」でSELECT now()を実行すると21時間前の日時が返りました。

原因

検証サーバーのtimezoneの設定がGMT+12に設定されており、さらに9:00が加算されたことで、21(12 + 9)時間のズレとなったのです。

疑問点

最初のテーブルのデータの登録日は、なぜ正しかったのか？

最初のテーブルは、CSV出力した段階で登録日(PCのシステム日付)がセットされており、COPY句でCSVデータから直接テーブルに登録していたため。
もう一方のテーブルは、登録日にto_char(now(),'yyyymmdd')、時刻にto_char(now(),'hh24miss')としてデータベース上の日時をセットしていた。

SELECT now()は、なぜ正しかったのか？

これは「A5:SQL Mk-2」の罠でした。

A5:SQL Mk-2では set session timezone の設定にかかわらず、クライアントOSのタイムゾーン情報に従って文字列化する

最後に

本番環境ではなく、開発中の検証サーバーということもあって、同僚と和気藹々と談笑しながら調べてました。本番環境だったらゾッとしますね。

帰宅してから、この記事を書く上でChat GPTに21時間ズレについて質問してみました。正しい回答が返ってきましたね。

Chat GPTに質問

一部省略しています。

プロンプト

PostgresSQLでデータ登録すると日本時間から21時間前の登録日時がセットされました。これから推測される原因は何ですか

回答

PostgreSQLでデータ登録時に日本時間から21時間前の登録日時がセットされるという問題の原因として、いくつかの可能性が考えられます。主な原因を以下に挙げます。

タイムゾーン設定の問題:
PostgreSQLサーバーのタイムゾーン設定が日本時間（JST, UTC+9）ではなく、別のタイムゾーンに設定されている可能性があります。21時間の差から推測すると、サーバーがUTC-12のタイムゾーンに設定されている可能性があります。

対処方法
サーバーのタイムゾーンを確認・設定
PostgreSQLサーバーのタイムゾーンを確認し、適切に設定します。例えば、日本時間に設定する場合は、postgresql.confファイルに以下を追加します。

コードをコピーする

timezone = 'Asia/Tokyo'