LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@atokinHabitat Hub

PostgreSQL: TIMESTAMP型とTIMESTAMPTZ型のタイムゾーン変換

Last updated at Posted at 2025-01-03

はじめに

日時の管理はデータベース設計において重要な要素です。PostgreSQLには、TIMESTAMP型とTIMESTAMPTZ型の2つの日時型があり、タイムゾーン設定が結果に与える影響を理解することが大切です。本記事では、これらの型の違いと、タイムゾーン設定の動作、およびそれぞれを適切に変換する方法をご紹介します。

TIMESTAMP型とTIMESTAMPTZ型の違い

それでは、これらの型がどのように動作し、タイムゾーン設定がどのように結果に影響するのかを確認するため、以下のSQLスクリプトを実行します。
※スクリプト中のコメント--(1-1):等は実行結果を示します

タイムゾーンがUTCの場合：


SET timezone TO 'UTC';

select
--1.文字列をJSTとして解釈
	'2024-08-02 00:00:00' AT TIME ZONE 'JST', 
    --(1-1): 2024-08-02 09:00:00
	pg_typeof('2024-08-02 00:00:00' AT TIME ZONE 'JST'),
    --(1-2): timestamp without time zone

 --2.TIMESTAMP型にキャスト
	'2024-08-02 00:00:00'::TIMESTAMP, 
    --(2-1): 2024-08-02 00:00:00
	pg_typeof('2024-08-02 00:00:00'::TIMESTAMP),
    --(2-2): timestamp without time zone

 --3.TIMESTAMP型をJSTとして解釈
	'2024-08-02 00:00:00'::TIMESTAMP AT TIME ZONE 'JST',
    --(3-1): 2024-08-01 15:00:00+00 ※表示はクライアントツールのタイムゾーンに依存する場合あり
	pg_typeof('2024-08-02 00:00:00'::TIMESTAMP AT TIME ZONE 'JST'),
    --(3-2): timestamp with time zone
 
 --4.TIMESTAMPTZ型にキャスト
	'2024-08-02 00:00:00'::TIMESTAMPTZ,
    --(4-1): 2024-08-02 00:00:00+00　※表示はクライアントツールのタイムゾーンに依存する場合あり
	pg_typeof('2024-08-02 00:00:00'::TIMESTAMPTZ),
    --(4-2): timestamp with time zone

--5.TIMESTAMPTZ型をJSTとして解釈
	'2024-08-02 00:00:00'::TIMESTAMPTZ AT TIME ZONE 'JST',
    --(5-1): 2024-08-02 09:00:00
	pg_typeof('2024-08-02 00:00:00'::TIMESTAMPTZ AT TIME ZONE 'JST')
    --(5-2): timestamp without time zone	
    ;

同様に、タイムゾーンをJSTに設定して同じSELECT文を実行してみましょう。

SET timezone TO 'Asia/Tokyo';
-- 以下省略（同じクエリを使用）

タイムゾーン（UTC vs. JST）の結果は以下のようになります。

タイムゾーン UTC JST
1-1 文字列をJSTとして解釈 2024-08-02 09:00:00 2024-08-02 00:00:00
1-2 型 timestamp without time zone timestamp without time zone
2-1 TIMESTAMP型にキャスト 2024-08-02 00:00:00 2024-08-02 00:00:00
2-2 型 timestamp without time zone timestamp without time zone
3-1 TIMESTAMP型をJSTとして解釈　※ 2024-08-01 15:00:00 +00 2024-08-02 00:00:00 +09
3-2 型 timestamp with time zone timestamp with time zone
4-1 TIMESTAMPTZ型にキャスト　※ 2024-08-02 00:00:00 +00 2024-08-02 00:00:00 +09
4-2 型 timestamp with time zone timestamp with time zone
5-1 TIMESTAMPTZ型をJSTとして解釈 2024-08-02 09:00:00 2024-08-02 00:00:00
5-3 型 timestamp without time zone timestamp without time zone

※表示されるタイムゾーンの結果について：
表示されるタイムゾーンの結果は、クライアントツールのタイムゾーンの設定に依存する場合があります。
私はDBeaverを使用していますが、DBeaverを実行する際のJavaのタイムゾーンが日本語の環境のため、以下の"Data Formats"の設定にて「Disable date/time formatting」をオンにしなければ、データベースのタイムゾーンではなく常に「+0900」のタイムゾーンで表示されてしまいます。

Screenshot 2025-01-03 at 16.28.50.png

他にも設定ファイルを変更してタイムゾーンを固定する方法があるようです。
以下の記事に記載がありますので、気になる方はこちらも確認してみてください。

参考記事：

解説

値に注目

タイムゾーンが異なる場合に、値が異なるのは以下です。

  • 文字列リテラルをAT TIME ZONEで変換（1-1）
    • 文字列リテラルは、まずデータベースのタイムゾーンとして解釈され、その後指定したタイムゾーンに変換されます
    • DBのタイムゾーンと「AT TIME ZONE」で指定したタイムゾーンが異なる場合は変換後の時刻が変わります
  • TIMESTAMP型をJSTとして解釈（3-1）
    • データベースのタイムゾーンで解釈された値が指定のタイムゾーンに変換されます
  • 文字列リテラルをTIMESTAMPTZ型にキャスト（4-1）
    • キャスト時にデータベースのタイムゾーンが適用されます
  • (4-1)の結果を「AT TIME ZONE」で再変換（5-1）
    • キャスト結果が指定タイムゾーンに再変換されます

型の違いに注目

型に着目すると、「AT TIME ZONE」の返り値の型が変わっています。

  • 「timestamp without time zone」型に対して「AT TIME ZONE」を実行すると返り値は「timestamp with time zone」型になります（2-1 vs. 3-1）
    • 3-1.は、指定した時間は、「AT TIME ZONE zone」のタイムゾーンでは何時？を示す結果になります

例えばDBに格納されたTIMESTAMPTZ型の日時（UTC）に対して、条件を任意のタイムゾーンの値（JST）で条件を指定して抽出したいような場合は以下のように記述します。
例) ('2024-08-02 00:00:00+09'::TIMESTAMPTZ AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'
※任意のタイムゾーンの値がDBのタイムゾーンでは何時かをTIMESTAMP型で取得してから、最後の「AT TIME ZONE」でTIMESTAMPTZ型の日時を取得する

  • 「timestamp with time zone」型に対して「AT TIME ZONE」を実行すると返り値は「timestamp without time zone」型になる（4-1 vs. 5-1）
    • 5-1.は、指定した時間は、DBのタイムゾーンでは何時かを解釈したうえで、「AT TIME ZONE zone」のタイムゾーンでは何時？を示す結果になります

例えばDBに格納されたTIMESTAMP型の日時（UTC）に対して、条件を任意のタイムゾーンの値（JST）で条件を指定して抽出したいような場合は以下のように記述します。
例) '2024-08-02 00:00:00+09'::TIMESTAMPTZ AT TIME ZONE 'UTC'
※任意のタイムゾーンの値がDBのタイムゾーンでは何時かをTIMESTAMP型で取得する

公式ドキュメントに記載されている以下の表に従った挙動となっていることがわかります。

まとめ

本記事では、PostgreSQLのタイムゾーンに関連する操作について、特に「AT TIME ZONE」関数を使った変換処理に焦点を当てて解説しました。
また、データベース内の日付がUTCや他のタイムゾーンで保存されている場合、「AT TIME ZONE」で目的のタイムゾーンに合わせて変換できることを確認しました。

改めてポイントとまとめます。

  • タイムゾーン変換

    • 文字列リテラルやタイムスタンプを異なるタイムゾーンに変換する方法を示しました。出力結果はデータベースのタイムゾーンと「AT TIME ZONE」で指定したタイムゾーンに依存するため、それぞれのタイムゾーンについて整理しておく必要があります

  • 型の変換

    • 「AT TIME ZONE」を使うことで、timestamp without time zone型からtimestamp with time zone型に変換されたり、逆にtimestamp with time zone型からtimestamp without time zone型に変換されたりします。この動作を理解することで、タイムゾーンを考慮した適切なクエリが書けるようになります

タイムゾーンの変換は煩雑になりがちですが、これらの関数を活用することで、異なるタイムゾーン間で正確に時刻を処理できます。基本的なことではありますが、公式ドキュメントを参考にしながら、状況に応じたタイムゾーン処理を行うことが、データの整合性を保つ上で非常に重要です。

本記事がタイムゾーン問題に悩む開発者の方々の参考になれば嬉しいです。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?