PostgreSQL の日付と時刻の操作

はじめに

この実験（Lab）では、PostgreSQL の日付と時刻の操作について、特にタイムゾーンの処理と計算に焦点を当てて学習します。まず、タイムゾーン情報を含むタイムスタンプをデータベーステーブルに挿入し、異なる地理的場所で発生するイベントをどのように保存するかを実演します。

次に、EXTRACT や INTERVAL などの関数を使用して、日付と時刻のデータを操作およびクエリする方法を学びます。タイムゾーンを跨いだデータのクエリや、イベント間の時間差の計算方法を習得し、PostgreSQL で時間データを管理するための実践的なスキルを身につけます。

Skills Graph

PostgreSQL への接続と events テーブルの作成

このステップでは、PostgreSQL データベースに接続し、events テーブルを作成します。このテーブルには、イベント名と、それに対応するタイムゾーン情報付きのタイムスタンプを格納します。

まず、ターミナルを開きます。postgres ユーザーとして PostgreSQL データベースに接続するには、次のコマンドを実行します。

sudo -u postgres psql

このコマンドは、PostgreSQL のインタラクティブターミナル（psql シェルとも呼ばれます）を開きます。このシェルを使用して SQL コマンドを実行します。

次に、events テーブルを作成しましょう。次の SQL コマンドを実行します。

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

このコマンドは、events という名前のテーブルを、次の 3 つの列で作成します。

• id: 各新しいイベントに対して自動的にインクリメントされるシリアル（SERIAL）の主キー（PRIMARY KEY）です。
• event_name: イベントの名前を含む文字列です。
• event_time: タイムゾーン情報付きのタイムスタンプ (TIMESTAMPTZ) です。このデータ型は、異なるタイムゾーンで発生するイベントを処理するために重要です。

テーブルの作成を確認するには、次のコマンドを使用してテーブル構造を記述できます。

\d events

これにより、テーブルスキーマが表示され、events テーブルが正常に作成されたことが確認できます。

タイムゾーン付きのタイムスタンプの挿入

このステップでは、各イベントのタイムゾーンを指定して、events テーブルにデータを挿入します。これは、異なる地理的場所で発生するイベントをどのように保存するかを実演するものです。

psql シェル（前のステップで接続済み）を使用して、次のデータを events テーブルに挿入します。

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00-08 America/Los_Angeles');

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00+00 Europe/London');

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00-08 America/Los_Angeles');

これらの INSERT ステートメントでは：

• '2024-01-20 10:00:00-08 America/Los_Angeles' は、2024 年 1 月 20 日午前 10 時（America/Los_Angeles タイムゾーン、UTC-8）のタイムスタンプを指定します。
• '2024-01-20 18:00:00+00 Europe/London' は、2024 年 1 月 20 日午後 6 時（Europe/London タイムゾーン、UTC+0）のタイムスタンプを指定します。

データの挿入を確認するには、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)

PostgreSQL はタイムスタンプを UTC (協定世界時) で保存することに注意してください。タイムゾーン付きのタイムスタンプを挿入すると、PostgreSQL はそれを UTC に変換して内部的に保存します。

EXTRACT と AT TIME ZONE の使用

このステップでは、EXTRACT 関数を使用してタイムスタンプの特定の部分（年、月、日など）を抽出し、AT TIME ZONE 句を使用してタイムスタンプを異なるタイムゾーンに変換する方法を学びます。

まず、event_time 列から年を抽出してみましょう。次のクエリを実行します。

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

このクエリでは、EXTRACT 関数を使用して event_time 列から年を取得し、event_year としてエイリアス（alias）を付けています。出力は次のようになります。

   event_name   | event_year
----------------+------------
 Conference Call |       2024
 Project Meeting |       2024
 Follow-up Meeting |       2024
(3 rows)

次に、AT TIME ZONE 句を使用して event_time を特定のタイムゾーンに変換する方法を見てみましょう。event_time を America/Los_Angeles タイムゾーンで表示するには、次のクエリを使用します。

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

このクエリは、event_time を America/Los_Angeles タイムゾーンに変換し、los_angeles_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 04:00:00
(3 rows)

EXTRACT と AT TIME ZONE を組み合わせて、特定のタイムゾーンのタイムスタンプの特定のコンポーネントを抽出できます。たとえば、America/Los_Angeles タイムゾーンでのイベントの時間を調べるには、次のクエリを使用します。

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'America/Los_Angeles') AS event_hour_la FROM events;

これにより、America/Los_Angeles タイムゾーンでの各イベントの時間が出力されます。

INTERVAL を使用した時間差の計算

このステップでは、INTERVAL データ型を使用して、2 つのイベント間の時間差を計算する方法を学びます。

時間差を計算するには、あるタイムスタンプから別のタイムスタンプを減算します。結果は、差を表す INTERVAL 値になります。

「Follow-up Meeting（フォローアップミーティング）」と「Conference Call（電話会議）」の間の時間差を計算してみましょう。これを行うには、サブクエリを使用するか、テーブル自体に結合（join）します。以下は、結合を使用した例です。

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 テーブル自体に結合し、テーブルに e1 および e2 としてエイリアス（alias）を付けています。次に、両方のテーブルから event_name を選択し、「Follow-up Meeting」の event_time と「Conference Call」の event_time の間の時間差を計算します。出力は次のようになります。

    event1     |     event2      |   time_difference
----------------+-----------------+---------------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

time_difference 列は、2 つのイベント間の間隔を示しています。

EXTRACT 関数を使用して、INTERVAL 値から特定のコンポーネントを抽出することもできます。たとえば、時間差から日数を抽出するには、次のクエリを使用できます。

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    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';

このクエリは、time_difference から日数を抽出し、days_difference として表示します。

最後に、PostgreSQL シェルを終了します。

\q

これにより、ターミナルに戻ります。

まとめ

この実験（Lab）では、PostgreSQL の日付と時刻の操作について学習しました。具体的には、以下の方法を学びました。

• タイムゾーン情報を含むタイムスタンプを格納するための TIMESTAMPTZ 列を持つテーブルを作成する。
• 各イベントのタイムゾーンを指定して、テーブルにデータを挿入する。
• EXTRACT 関数を使用して、年、月、日など、タイムスタンプの特定の部分を抽出する。
• AT TIME ZONE 句を使用して、タイムスタンプを異なるタイムゾーンに変換する。
• INTERVAL データ型を使用して、2 つのタイムスタンプ間の時間差を計算する。

これらのスキルは、PostgreSQL で時間的なデータ（temporal data）を管理する上で不可欠であり、特に異なる地理的な場所で発生するイベントを扱う場合に重要です。