PostgreSQLで時差を気にせず！UTCタイムゾーンをEST(現地時間)に変換する賢い方法

2024-06-19

PostgreSQL で UTC タイムゾーンを EST (現地時間) に変換する方法

以下は、UTC タイムゾーンのタイムスタンプを EST に変換する例です。

SELECT my_timestamp_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York';

このクエリは、my_timestamp_utc カラムに格納されている UTC タイムスタンプを、EST (ニューヨーク時間) に変換して返します。

ポイント:

PostgreSQL はすべてのタイムスタンプを内部的に UTC で保存します。
AT TIME ZONE 関数は、データが保存されているタイムゾーンを意識せずに、必要なタイムゾーンに変換することができます。
EST以外にも、任意のタイムゾーン名に置き換えることができます。

代替方法:

EXTRACT 関数と pg_timezone_names テーブルを使用して、UTC タイムスタンプから EST オフセットを取得し、それを加算する方法もあります。

SELECT my_timestamp_utc + interval '00:00:00' * EXTRACT(hour FROM pg_timezone_names.utc_offset
                                   WHERE timezone_name = 'America/New_York') AT TIME ZONE 'UTC';

AT TIME ZONE 関数はシンプルで分かりやすい書き方です。
EXTRACT 関数と pg_timezone_names テーブルを使用した方法は、より柔軟な制御が可能です。

補足

PostgreSQL には、様々なタイムゾーン関連の関数とテーブルが用意されています。詳細は PostgreSQL ドキュメントを参照してください。
時刻変換を行う際には、サマータイムの影響も考慮する必要があります。PostgreSQL はサマータイムを自動的に処理することができます。

PostgreSQL で UTC タイムゾーンを EST (現地時間) に変換するサンプルコード

AT TIME ZONE 関数を使用する場合

-- UTC タイムスタンプを仮定
my_timestamp_utc = '2024-06-18 18:22:00'::timestamptz;

-- UTC タイムゾーンを EST (ニューヨーク時間) に変換
my_timestamp_est = my_timestamp_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York';

-- 結果を表示
SELECT my_timestamp_est;

このコードは、以下の結果を出力します。

my_timestamp_est
-----------------
2024-06-18 13:22:00-04

EXTRACT 関数と pg_timezone_names テーブルを使用する場合

-- UTC タイムスタンプを仮定
my_timestamp_utc = '2024-06-18 18:22:00'::timestamptz;

-- EST オフセットを取得
est_offset = interval '00:00:00' * EXTRACT(hour FROM pg_timezone_names.utc_offset
                                       WHERE timezone_name = 'America/New_York');

-- UTC タイムスタンプに EST オフセットを加算して EST (ニューヨーク時間) に変換
my_timestamp_est = my_timestamp_utc + est_offset AT TIME ZONE 'UTC';

-- 結果を表示
SELECT my_timestamp_est;

my_timestamp_est
-----------------
2024-06-18 13:22:00-04

説明:

上記のコードは、いずれも my_timestamp_utc という名前の UTC タイムスタンプ変数を使用しています。この変数は、ご自身の環境に合わせて変更してください。
AT TIME ZONE 関数を使用する場合は、引数として変換先のタイムゾーン名を指定する必要があります。ここでは、EST (ニューヨーク時間) を表す America/New_York タイムゾーンを使用しています。
EXTRACT 関数と pg_timezone_names テーブルを使用する場合は、まず pg_timezone_names テーブルから EST オフセットを取得する必要があります。その後、そのオフセットを UTC タイムスタンプに加算することで、EST (ニューヨーク時間) に変換することができます。

補足:

これらのサンプルコードは、基本的な変換方法を示しています。実際の運用環境では、エラー処理や接続管理などの処理を追加する必要があります。

PostgreSQL で UTC タイムゾーンを EST (現地時間) に変換するその他の方法

WITH TIME ZONE 句を使用する場合

-- UTC タイムスタンプを仮定
my_timestamp_utc = '2024-06-18 18:22:00'::timestamptz;

-- UTC タイムスタンプとタイムゾーン名を指定して、EST (ニューヨーク時間) に変換
my_timestamp_est = my_timestamp_utc WITH TIME ZONE 'UTC' AT TIME ZONE 'America/New_York';

-- 結果を表示
SELECT my_timestamp_est;

このコードは、WITH TIME ZONE 句を使用して、UTC タイムスタンプと変換先のタイムゾーン名を同時に指定する方法です。

DEFAULT_TIME_ZONE 設定を使用する場合

-- デフォルトタイムゾーンを EST (ニューヨーク時間) に設定
SET TIME ZONE 'America/New_York';

-- UTC タイムスタンプを仮定
my_timestamp_utc = '2024-06-18 18:22:00'::timestamptz;

-- UTC タイムスタンプをデフォルトタイムゾーンに変換
my_timestamp_est = my_timestamp_utc;

-- 結果を表示
SELECT my_timestamp_est;

このコードは、SET TIME ZONE 構文を使用して、デフォルトタイムゾーンを EST (ニューヨーク時間) に設定し、そのデフォルトタイムゾーンを使用して UTC タイムスタンプを自動的に変換する方法です。

-- UTC タイムスタンプを仮定
my_timestamp_utc = '2024-06-18 18:22:00'::timestamptz;

-- UTC 文字列に変換
my_timestamp_utc_str = to_char(my_timestamp_utc, 'YYYY-MM-DD HH24:MI:SS'::text);

-- EST オフセットを取得
est_offset = interval '00:00:00' * EXTRACT(hour FROM pg_timezone_names.utc_offset
                                       WHERE timezone_name = 'America/New_York');

-- UTC 文字列に EST オフセットを加算して EST 文字列に変換
my_timestamp_est_str = to_char(my_timestamp_utc + est_offset, 'YYYY-MM-DD HH24:MI:SS'::text);

-- 結果を表示
SELECT my_timestamp_est_str;