はじめに
この実験 (Lab) では、PostgreSQL の強力な日付・時刻操作について探求します。異なる地理的場所からのデータを扱うアプリケーションにとって不可欠なスキルである、タイムゾーン情報を含むタイムスタンプの扱い方を学びます。
まず、タイムゾーンを意識したデータ格納を確実にするために TIMESTAMPTZ データ型を使用したイベントデータを格納するテーブルを作成します。次に、異なるタイムゾーンで発生したイベントのデータを挿入し、PostgreSQL がどのようにデータを正規化するかを学びます。最後に、EXTRACT、AT TIME ZONE 句などの関数や、INTERVAL を使用した時間差の計算を用いて、この時間データをクエリおよび操作する練習を行います。
時間ベースのイベント用テーブルを作成する
このステップでは、PostgreSQL データベースに接続し、イベント情報を格納するためのテーブルを作成します。このテーブルでは、さまざまなタイムゾーンからのタイムスタンプを正確に格納するために不可欠な TIMESTAMPTZ データ型を使用します。
TIMESTAMPTZ の理解
PostgreSQL の TIMESTAMPTZ (timestamp with time zone) データ型は、世界中の日付と時刻を扱うように設計されています。特定のタイムゾーンを持つ値を挿入すると、PostgreSQL はそれを格納のために協定世界時 (UTC) に変換します。この標準化により、元のタイムゾーンに関係なく、時刻の比較や計算が容易になります。
ステップ 1: PostgreSQL への接続
まず、ターミナルを開きます。psql 対話型シェルを使用して PostgreSQL データベースに接続します。以降のすべての SQL コマンドはこのシェルで実行します。
sudo -u postgres psql
これで、postgres=# のような PostgreSQL プロンプトが表示されます。
ステップ 2: events テーブルの作成
次に、以下の SQL コマンドを実行して events テーブルを作成します。このテーブルは、各イベントの ID、名前、および時刻を格納します。
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(255),
event_time TIMESTAMPTZ
);
このコマンドは、3 つの列を持つ events という名前のテーブルを作成します。
id: 自動インクリメントされる主キー。event_name: イベントの名前を格納するテキストフィールド。event_time: タイムゾーンをサポートするタイムスタンプ。
ステップ 3: テーブル作成の確認
テーブルが正しく作成されたことを確認するには、\d コマンドを使用してその構造を調べることができます。
\d events
events テーブルのスキーマが表示され、その列とデータ型が確認できるはずです。
Table "public.events"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
event_name | character varying(255) | | |
event_time | timestamp with time zone | | |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
これで、時間ベースのデータを格納するためのテーブルのセットアップが完了しました。
タイムゾーン情報を含むデータを挿入する
このステップでは、events テーブルにレコードを挿入します。各レコードには特定のタイムゾーンに関連付けられたタイムスタンプが含まれ、PostgreSQL が異なる地理的場所からのデータをどのように処理するかを示します。
ステップ 1: イベントデータの挿入
psql シェル内で、以下の INSERT ステートメントを実行して、テーブルに 3 つのイベントを追加します。各 event_time にタイムゾーン識別子が含まれていることに注意してください。
ロサンゼルス (UTC-8) からのカンファレンス コール イベントを挿入します。
INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');
ロンドン (UTC+0) からのプロジェクト会議を挿入します。
INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');
ロサンゼルスからのフォローアップ会議も挿入します。
INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');
ステップ 2: データ挿入の確認
PostgreSQL がこのデータをどのように格納したかを確認するには、events テーブルをクエリします。
SELECT * FROM events;
以下のような出力が表示されるはずです。
id | event_name | event_time
----+-------------------+------------------------
1 | Conference Call | 2024-01-20 18:00:00+00
2 | Project Meeting | 2024-01-20 18:00:00+00
3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)
すべての event_time 値が UTC (オフセット +00 で示される) で表示されていることに注意してください。例えば、ロサンゼルス (UTC-8) の 10:00:00 は UTC の 18:00:00 に変換されました。この内部変換により、一貫した時間ベースの比較が可能になります。
タイムスタンプのクエリとフォーマット
UTC で保存されたデータを、必要な任意のタイムゾーンでクエリおよび表示できるようになりました。このステップでは、EXTRACT 関数と AT TIME ZONE 句を使用して、タイムスタンプを操作およびフォーマットします。
ステップ 1: EXTRACT を使用したタイムスタンプ部分の抽出
EXTRACT 関数を使用すると、タイムスタンプから特定のコンポーネント（年、月、時など）を抽出できます。各イベントの年を取得しましょう。
SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;
このクエリは、保存されている UTC タイムスタンプから年を抽出します。
event_name | event_year
-------------------+------------
Conference Call | 2024
Project Meeting | 2024
Follow-up Meeting | 2024
(3 rows)
ステップ 2: AT TIME ZONE を使用したタイムスタンプの変換
保存されている UTC 時刻を特定のローカルタイムゾーンで表示するには、AT TIME ZONE 句を使用します。すべてのイベント時刻を America/Los_Angeles タイムゾーンに変換してみましょう。
SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;
このクエリは、UTC の event_time を取得し、ロサンゼルスでの同等のローカル時刻を表示します。
event_name | los_angeles_time
-------------------+---------------------
Conference Call | 2024-01-20 10:00:00
Project Meeting | 2024-01-20 10:00:00
Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)
ご覧のとおり、「Project Meeting」は UTC で 18:00:00 に発生しましたが、ロサンゼルス時間では正しく 10:00:00 と表示されています。
ステップ 3: EXTRACT と AT TIME ZONE の組み合わせ
これらの機能を組み合わせて、より強力なクエリを作成できます。たとえば、Europe/London タイムゾーンで発生した各イベントの時刻を検索するには、次のようにします。
SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;
このクエリは、まず時刻をロンドンタイムゾーンに変換し、次に時を抽出します。
event_name | event_hour_london
-------------------+-------------------
Conference Call | 18
Project Meeting | 18
Follow-up Meeting | 20
(3 rows)
時間差の計算
一般的な要件として、2 つのイベント間の期間を計算することがあります。PostgreSQL では、一方のタイムスタンプからもう一方を減算すると、INTERVAL データ型が得られます。これは時間の経過を表します。
ステップ 1: 2 つのイベント間の差の計算
「Conference Call」と「Follow-up Meeting」の間の経過時間を調べてみましょう。これは、events テーブルをそれ自身に結合することで実現できます。
SELECT
e1.event_name AS event1,
e2.event_name AS event2,
e2.event_time - e1.event_time AS time_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';
このクエリは次のように機能します。
eventsテーブルの 2 つの仮想インスタンスe1とe2を作成します。e1を 'Conference Call' の行のみにフィルタリングします。e2を 'Follow-up Meeting' の行のみにフィルタリングします。e2からe1のevent_timeを減算します。
結果は INTERVAL 値になります。
event1 | event2 | time_difference
-----------------+-------------------+-----------------
Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)
時間差は 1 日と 2 時間です。
ステップ 2: INTERVAL からのコンポーネントの抽出
INTERVAL に対して EXTRACT を使用して、期間の特定の部分（日数や時間数など）を取得することもできます。前の計算から日数のみを抽出してみましょう。
SELECT
EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';
これにより、日数に関する数値が得られます。
days_difference
-----------------
1
(1 row)
ステップ 3: PostgreSQL の終了
この実験の演習は完了しました。これで psql シェルを終了し、メインのターミナルに戻ることができます。
\q
まとめ
この実験では、PostgreSQL における基本的な日付と時刻の操作について学習しました。以下のことを学びました。
- タイムゾーンを認識する情報を正しく保存するために、
TIMESTAMPTZデータ型を使用してテーブルを作成する方法。 - 指定されたタイムゾーンを持つタイムスタンプを挿入し、PostgreSQL がそれらを保存のために UTC にどのように変換するかを観察する方法。
EXTRACT関数を使用して、タイムスタンプから年や時などの特定のコンポーネントを取得する方法。AT TIME ZONE句を使用して、保存された UTC 時刻を任意のローカルタイムゾーンで表示する方法。- 2 つのイベント間の期間を計算し、それが
INTERVALとなり、そのインターバルからコンポーネントを抽出する方法。
これらのスキルは、異なる地域間で時間データを正確に管理する堅牢なアプリケーションを構築するための基本となります。
