PostgreSQLでインターバルから合計日数・時間・分を取り出す方法

2024年8月28日著者: Matthew Rathbone

PostgreSQLの「interval」は時間の長さを表します。2つのTIMESTAMP値を引き算した結果として得られるほか、INTERVALキーワードで明示的に定義することもできます。

例を示します:

SELECT INTERVAL '2 days 3 hours 40 minutes' AS example_interval;

期待される出力:

example_interval
-------------------
2 days 03:40:00

日・時間・分を取り出す

日数を取り出す

インターバルから合計日数を取り出すには:

SELECT EXTRACT(DAY FROM INTERVAL '5 days 4 hours 30 minutes') AS total_days;

期待される出力:

total_days
------------
5

時間を取り出す

時間を取り出す場合、EXTRACT関数はインターバルの「時」の部分だけを返し、日数を含めた合計時間は返しません。例えば:

SELECT EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS hours_component;

期待される出力:

hours_component
-----------------
4

日数を含めた合計時間を求めるには、少し計算が必要です:

SELECT EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 +
       EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS total_hours;

期待される出力:

total_hours
------------
52

分を取り出す

時間と同様に、分を取り出すとインターバルの「分」の部分だけが返されます:

SELECT EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS minutes_component;

期待される出力:

minutes_component
-------------------
30

インターバルの合計分数を求めるには、次のように計算します:

SELECT (EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 * 60) +
       (EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') * 60) +
       EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS total_minutes;

期待される出力:

total_minutes
---------------
3150

実践例

テーブルを使った実践的な例を見てみましょう。さまざまなイベントの開始と終了のタイムスタンプを保存するeventsというテーブルがあるとします:

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

INSERT INTO events (start_time, end_time)
VALUES 
    ('2024-08-01 08:00:00', '2024-08-03 12:30:00'),
    ('2024-08-05 09:15:00', '2024-08-05 18:45:00');

各イベントのインターバルを計算し、合計日数・時間・分を取り出せます:

SELECT 
    event_id,
    end_time - start_time AS duration,
    EXTRACT(DAY FROM end_time - start_time) AS total_days,
    (EXTRACT(DAY FROM end_time - start_time) * 24 +
     EXTRACT(HOUR FROM end_time - start_time)) AS total_hours,
    (EXTRACT(DAY FROM end_time - start_time) * 24 * 60 +
     EXTRACT(HOUR FROM end_time - start_time) * 60 +
     EXTRACT(MINUTE FROM end_time - start_time)) AS total_minutes
FROM events;

期待される出力:

event_id |   duration    | total_days | total_hours | total_minutes
---------+---------------+------------+-------------+--------------
       1 | 2 days 04:30  |          2 |          52 |          3150
       2 | 09:30:00      |          0 |           9 |           570