PostgreSQLで日時からN分前を計算するにはINTERVALを引くだけでいい

  • PostgreSQL
  • SQL

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

特定の日時からN分前を計算したい時、PostgreSQLでは日時型からINTERVALを引くだけでいい。用途別に3パターンある。

カラムの日時から計算する

テーブルのカラムに対してそのまま引き算する。

SELECT your_column - INTERVAL '10 minutes' AS ten_minutes_before
FROM your_table;

固定の日時から計算する

特定の日時をリテラルで書いて計算する場合。

SELECT TIMESTAMP '2023-01-01 12:00:00' - INTERVAL '10 minutes' AS ten_minutes_before;

現在時刻から計算する

現在時刻を基準にするならnow()を使う。

SELECT now() - INTERVAL '10 minutes' AS ten_minutes_before;

INTERVALの書き方のバリエーション

INTERVAL '10 minutes'の他にINTERVAL '10 min'のような省略形や、SQL標準寄りのINTERVAL '10' MINUTEという書き方もある。どれでも動くが、プロジェクト内で表記は揃えておいた方が読みやすい。

補足：now()の注意点

now()は関数名から実行のたびに時刻が進むように見えるが、実際はトランザクション開始時刻を返す。同じトランザクション内で何度呼んでも同じ値になる。CURRENT_TIMESTAMPも同じ挙動。

BEGIN;
SELECT now(); -- 12:00:00.000
-- ここで数秒処理が走っても
SELECT now(); -- やっぱり 12:00:00.000
COMMIT;

これはSQL標準の要求で、1つのトランザクション内では時刻を固定して一貫性を保つための仕様。

トランザクション中でも刻々と変化するリアルタイムの時刻が欲しい場合はclock_timestamp()を使う。処理時間を計測したい時なんかはnow()ではなくclock_timestamp()を使わないと差分が0になる。

SELECT clock_timestamp(); -- 呼ぶたびに実際の現在時刻