【postgres】二つの日時の差を秒単位、分単位、時単位で取得する方法

こんにちは、てとです。
最近は要件定義・設計が終わり、ガリガリとコーディングをしています。
今回、タイトルの通り、二つの日時の差を秒単位で求めるSQLを作る必要があり、色々調べて苦労したので、備忘の意味も込めてここに書いておきます。
コピペ利用は自己責任でどうぞ。
各関数の説明も一緒に書いておくので、鵜呑みにせず必ず検証してくださいね。

SQL（秒単位）

「現在時刻」と「DBに登録されたyyyyMMddhhmmss形式の値」を比較する場合

SELECT 
  TRUNC(
    EXTRACT(
      EPOCH FROM TIMESTAMPTZ(
        TRANSACTION_TIMESTAMP()
      )
    )
  - EXTRACT(
      EPOCH FROM TIMESTAMPTZ(
        TO_TIMESTAMP(UPDATE_TIME, 'YYYYMMDDHH24MISS')
      )
    )
  ) as COMPARE_TIME
FROM
  TABLE_NAME;

-- UPDATE_TIMEはカラム名。値はyyyyMMddhhmmss形式の14桁とする

「DBに登録されたyyyyMMddhhmmss形式の値」の2つを比較する場合

SELECT 
  TRUNC(
    EXTRACT(
      EPOCH FROM TIMESTAMPTZ(
        TO_TIMESTAMP(UPDATE_TIME, 'YYYYMMDDHH24MISS')
      )
    )
  - EXTRACT(
      EPOCH FROM TIMESTAMPTZ(
        TO_TIMESTAMP(INSERT_TIME, 'YYYYMMDDHH24MISS')
      )
    )
  ) as COMPARE_TIME
FROM
  TABLE_NAME;

-- UPDATE_TIME、INSERT_TIMEはいずれもカラム名。両方とも値はyyyyMMddhhmmss形式の14桁とする

SQL（分単位）

SELECT 
  TRUNC(
      (
      EXTRACT(
        EPOCH FROM TIMESTAMPTZ(
          TRANSACTION_TIMESTAMP()
        )
      )
    - EXTRACT(
        EPOCH FROM TIMESTAMPTZ(
          TO_TIMESTAMP(UPDATE_TIME, 'YYYYMMDDHH24MISS')
        )
      ) / 60 -- 60で割ることで、秒から分へ単位を変更する
    )
  ) as COMPARE_TIME
FROM
  TABLE_NAME;

-- UPDATE_TIMEはカラム名。値はyyyyMMddhhmmss形式の14桁とする

SQL（分単位）

SELECT 
  TRUNC(
      (
      EXTRACT(
        EPOCH FROM TIMESTAMPTZ(
          TRANSACTION_TIMESTAMP()
        )
      )
    - EXTRACT(
        EPOCH FROM TIMESTAMPTZ(
          TO_TIMESTAMP(UPDATE_TIME, 'YYYYMMDDHH24MISS')
        )
      ) / (60 * 24) -- (60*24)で割ることで、秒から分、分から時間へ単位を変更する
    )
  ) as COMPARE_TIME
FROM
  TABLE_NAME;

-- UPDATE_TIMEはカラム名。値はyyyyMMddhhmmss形式の14桁とする

SQLの説明

上記SQLには、EXTRACTがふたつ含まれており、「先のEXTRACT内の日時」から「後のEXTRACT内の日時」を差し引き、その差を「秒」の単位で返す動きをしています。
SQL内で使用している各関数の説明については、以下の通りとなります。

TRANSACTION_TIMESTAMP()

このSQLのトランザクション実行開始時刻を返します。

EXTRACT(EPOCH FROM ・・・)

EPOCH FROMとセットで使用することにより、FROM句に設定されたtimestamp with time zone型の値を、1970-01-01 00:00:00 UTCからの秒数から計算した秒数の差分で返します。
なお、timestamp with time zone型の値が1970/01/01以前の場合は、そこから計算した秒数の差分をマイナスの値で返します。

このSQLの場合、EXTRACT()単体またはEPOCH FROM単体で実行するとエラーになります。

TIMESTAMPTZ()

()内に設定されたtimestamp型の値を、timestamp with time zone型に変換して返します。

TRUNC()

小数点以下を切り捨てます。
たとえば下記SQLの実行結果は、10となります。

select trunc(10.75);

今回は切り捨てが仕様として正解だったため「trunc()」を使用しておりますが、四捨五入や切り上げの場合は下記の通りにすることで対応可能です。
・四捨五入：「trunc()」の代わりに「round()」を使用する
・切り上げ：「trunc()」の代わりに「ceil()」を使用する

trunc/round/ceilを使用することにより、小数点以下の処理をSQL内で完結することができ、
SQLの呼び出し元で余計なコーディングする手間を省くことができます。

まとめ

もっといい書き方があったら教えてほしいです。