PostgreSQLでintervalを「総時間:分:秒」形式にフォーマットする

  • PostgreSQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

検証環境はPostgreSQL 15。

1 day 00:15:25 みたいな interval 型の値を、24:15:25 のような「総時間:分:秒」形式で出したかった。日をまたいでも時間側に繰り上げて、24時間を超える表記にしたいというのが要件。

やりたいこと

考え方はシンプルで、EXTRACT(EPOCH FROM interval) で総秒数を取り出し、それを3600で割れば時間、余りを60で割れば分、さらに余りが秒になる。1 day は86400秒として計算されるので、日をまたいだ分もそのまま時間側に足し込まれる。

  • 1 day 00:15:25 → 87325秒 → 24:15:25
  • 2 days 03:45:10 → 186310秒 → 51:45:10

計算式自体はこれで合っている。

クエリ

example_table の timestamp_column から経過時間を計算して、category ごとに集計する想定。

WITH duration_calc AS (
  SELECT
    category,
    MAX(timestamp_column) AS max_timestamp,
    MIN(timestamp_column) AS min_timestamp,
    EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))::numeric AS total_seconds
  FROM
    example_table
  GROUP BY
    category
)
SELECT
  category,
  max_timestamp,
  min_timestamp,
  TO_CHAR(FLOOR(total_seconds / 3600), 'FM00') || ':' ||
  TO_CHAR(FLOOR((total_seconds % 3600) / 60), 'FM00') || ':' ||
  TO_CHAR(total_seconds % 60, 'FM00') AS elapsed_time
FROM
  duration_calc
ORDER BY
  category;

ポイントは2つ。

  1. MAX(timestamp_column) - MIN(timestamp_column) はCTEの中で1回だけ計算して total_seconds に持たせる。時・分・秒それぞれで同じ集約を書き直す必要がなくなる
  2. EXTRACT(EPOCH FROM ...) の結果は ::numeric にキャストしてから使う。EXTRACT() の戻り値は double precision だが、PostgreSQLの %（剰余）演算子は smallint / integer / bigint / numeric にしか対応しておらず、double precision のままだと ... % 3600 や ... % 60 の箇所で operator does not exist: double precision % integer になる。numeric にキャストしておけばここは問題なく通る

/ や FLOOR()、TO_CHAR() は double precision のままでも使えるので、キャストが必要なのは % を使う箇所だけ。

結果

  • 1 day 00:15:25 → 24:15:25
  • 2 days 03:45:10 → 51:45:10

狙い通りのフォーマットになった。24時間を超えても 24:15:25 51:45:10 のように総時間として繰り上がる。