PostgreSQLのクエリでタイムゾーンを変更する方法

はじめに

日時データ（created_at、updated_at など）を抽出するとき、タイムゾーンを"Asia/Tokyo"（JST=日本標準時）で抽出したいときがあると思います。このドキュメントでは、そんな時に使える日時データのタイムゾーンを変更する方法についてご紹介します。

タイムゾーンとは

まずタイムゾーンとは何かを知りましょう。 timestamp に付与されていセッション単位のパラメータで、セッションが実行するクエリに影響を与えます。このタイムゾーン設定があることで、データベースに保存されているタイムゾーン付きデータをセッションのタイムゾーンに合わせて表示を変更したり出来ます。デフォルトでは DB の設定ファイル（postgres.conf）に設定しているタイムゾーンで表示します。設定ファイルは UTC（世界標準時）で設定さているのが一般的です。

使うクエリ

早速、日時データのタイムゾーンを JST に変更して抽出するクエリの一例をご紹介します。

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo';

この日時データの後の”AT TIME ZONE 'タイムゾーン'”の部分がタイムゾーンを変更します。 UTC は協定世界時、Asia/Tokyo は日本標準時（JST）を意味します。このクエリは'2018-09-02 07:09:10'という日時データを日本標準時のタイムゾーンに変更して表示しています。これを実行すると以下のように表示されます。

元の時刻 07 時 09 分から 9 時間進んだ 16 時 09 分が表示されていますね。これは JST が UTC（協定世界時）よりも 9 時間進んだ時刻となっているからです。

もっと実務的な例をご紹介します。ユーザの情報が入った users テーブルが存在しているとして、以下のクエリを実行します。

SELECT created_at FROM USERS;

実行した結果がこちら。

これに”AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo'”を付与したクエリを実行します。

SELECT created_at　AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' FROM USERS;

実行結果がこちら。

各データが 9 時間進んだ日時になっていることがわかりますね。

おまけ

設定できるタイムゾーンの一覧を確認できるコマンドです。

SELECT * FROM pg_timezone_names;

これを実行するとタイムゾーンの一覧が表示されます。

参考文献

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「どんなことをお願いできるのか」「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。