【決定版】PostgreSQLでタイムスタンプ差を計算する3つの方法を徹底比較

2024-06-14

PostgreSQLにおけるタイムスタンプ差の取得（時間単位）

EXTRACT() 関数を使用する

最もシンプルで分かりやすい方法が、EXTRACT() 関数を使用する方法です。この関数は、タイムスタンプから指定された時間単位の値を抽出することができます。

SELECT
  EXTRACT(HOUR FROM timestamp_end - timestamp_start) AS time_diff_hours
FROM your_table;

このクエリは、your_table テーブルの timestamp_end と timestamp_start カラムの差を時間単位で抽出し、time_diff_hours という名前の別名で返します。

メリット:

シンプルで分かりやすい構文
他の関数との組み合わせが容易

小数点以下の時間情報が失われる

interval 型は、時間間隔を表すデータ型です。2つのタイムスタンプを直接引き算することで、時間間隔を計算することができます。

SELECT
  timestamp_end - timestamp_start AS time_diff_interval
FROM your_table;

EXTRACT() 関数よりもやや複雑な構文

age() 関数は、2つのタイムスタンプの差を年、月、週、日、時間、分、秒の単位でそれぞれ計算することができます。

SELECT
  EXTRACT(HOUR FROM age(timestamp_end, timestamp_start)) AS time_diff_hours
FROM your_table;

年、月、週などの単位も同時に取得できる

複雑なクエリになりやすい

結合クエリを使用する

複数のテーブルからタイムスタンプを取得し、差を計算する場合には、結合クエリを使用することができます。

SELECT
  t1.timestamp_end - t2.timestamp_start AS time_diff_hours
FROM your_table1 AS t1
JOIN your_table2 AS t2
ON t1.id = t2.id;

複数のテーブルからデータを結合できる

最適な方法の選択

上記のように、PostgreSQLには様々な方法でタイムスタンプ差の取得が可能です。最適な方法は、利用目的に応じて選択する必要があります。

シンプルで分かりやすい方法を求める場合は、EXTRACT() 関数を使用するのがおすすめです。
小数点以下の時間情報も保持したい場合は、interval 型を使用する必要があります。
年、月、週などの単位も同時に取得したい場合は、age() 関数を使用します。
複数のテーブルからデータを結合する場合は、結合クエリを使用します。

PostgreSQLにおけるタイムスタンプ差の取得（時間単位） - サンプルコード

EXTRACT() 関数を使用する

-- サンプルデータ
CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table (timestamp_start, timestamp_end)
VALUES
  ('2024-06-14 00:00:00', '2024-06-14 01:00:00'),
  ('2024-06-14 05:30:00', '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間単位）
SELECT
  id,
  EXTRACT(HOUR FROM timestamp_end - timestamp_start) AS time_diff_hours
FROM your_table;

interval 型を使用する

-- サンプルデータ
CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table (timestamp_start, timestamp_end)
VALUES
  ('2024-06-14 00:00:00', '2024-06-14 01:00:00'),
  ('2024-06-14 05:30:00', '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間間隔）
SELECT
  id,
  timestamp_end - timestamp_start AS time_diff_interval
FROM your_table;

age() 関数を使用する

-- サンプルデータ
CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table (timestamp_start, timestamp_end)
VALUES
  ('2024-06-14 00:00:00', '2024-06-14 01:00:00'),
  ('2024-06-14 05:30:00', '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間単位）
SELECT
  id,
  EXTRACT(HOUR FROM age(timestamp_end, timestamp_start)) AS time_diff_hours
FROM your_table;

結合クエリを使用する

-- サンプルデータ
CREATE TABLE your_table1 (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL
);

CREATE TABLE your_table2 (
  id SERIAL PRIMARY KEY,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table1 (id, timestamp_start)
VALUES
  (1, '2024-06-14 00:00:00'),
  (2, '2024-06-14 05:30:00');

INSERT INTO your_table2 (id, timestamp_end)
VALUES
  (1, '2024-06-14 01:00:00'),
  (2, '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間単位）
SELECT
  t1.id,
  EXTR

PostgreSQLにおけるタイムスタンプ差の取得（時間単位） - その他の方法

datediff() 関数は、PostgreSQL 12.0以降で導入された関数で、2つのタイムスタンプ間の差を日数、時間、分、秒の単位で計算することができます。

-- サンプルデータ
CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table (timestamp_start, timestamp_end)
VALUES
  ('2024-06-14 00:00:00', '2024-06-14 01:00:00'),
  ('2024-06-14 05:30:00', '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間単位）
SELECT
  id,
  datediff(hour, timestamp_start, timestamp_end) AS time_diff_hours
FROM your_table;

WITH ステートメントを使用すると、一時的な中間結果セットを作成し、その結果セットをクエリ内で参照することができます。この機能を活用することで、複雑な時間差計算をより分かりやすく記述することができます。

-- サンプルデータ
CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  timestamp_start TIMESTAMP NOT NULL,
  timestamp_end TIMESTAMP NOT NULL
);

INSERT INTO your_table (timestamp_start, timestamp_end)
VALUES
  ('2024-06-14 00:00:00', '2024-06-14 01:00:00'),
  ('2024-06-14 05:30:00', '2024-06-14 07:15:00');

-- タイムスタンプ差の取得（時間単位）
WITH time_diff AS (
  SELECT
    id,
    timestamp_end - timestamp_start AS time_diff
  FROM your_table
)
SELECT
  id,
  EXTRACT(HOUR FROM time_diff) AS time_diff_hours
FROM time_diff;