TIMESTAMPDIFF関数の使い方 - DBごとの違いと注意点

TIMESTAMPDIFF は全てのSQLデータベースに共通する標準関数ではありません。別製品へSQLを移す場合は、関数名だけでなく戻り値、引数順、差分の単位、端数や境界の数え方も置き換える必要があります。

TIMESTAMPDIFF is not a universal SQL function. MySQL, Oracle, PostgreSQL, SQL Server, Snowflake, BigQuery, and Spark-family engines use different names, argument rules, result types, and boundary semantics for datetime differences.

1. MySQL / MariaDBのTIMESTAMPDIFF

SELECT TIMESTAMPDIFF(DAY, '2026-07-01', '2026-07-22');
-- 21
SELECT TIMESTAMPDIFF(DAY, '2026-07-22', '2026-07-01');
-- -21

MySQLとMariaDBの構文は TIMESTAMPDIFF(unit, datetime_start, datetime_end) です。単位は MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR などです。start、endの順なので、endが後なら正、逆にすると符号が反転します。

SELECT TIMESTAMPDIFF(
  HOUR,
  '2026-07-21 23:30:00',
  '2026-07-22 01:15:00'
);
-- 1

指定単位の完全な経過数を整数で返すため、この例の1時間45分は 1 時間です。日数、月数、年数では単純な秒数の割り算とは結果が異なることがあります。特に暦月の端やうるう日は、実データに近い境界値で確認してください。

2. Oracleには同名関数がない

-- DATE同士: 結果は日数
SELECT end_date - start_date AS days_diff FROM events;
-- TIMESTAMP同士: 結果はINTERVAL
SELECT end_ts - start_ts AS elapsed FROM events;
-- 月単位
SELECT MONTHS_BETWEEN(end_date, start_date) FROM events;

Oracle Databaseには一般的なSQL関数としての TIMESTAMPDIFF はありません。DATE 同士の減算は日数を返し、TIMESTAMP 同士は INTERVAL 型を返します。月単位なら MONTHS_BETWEEN() を使います。

DATE 差分を時間へ直すなら日数に24、分ならさらに60を掛けられますが、TIMESTAMP の INTERVAL は数値と同じ扱いではありません。必要な単位を明確にして、対象バージョンで抽出方法を確認します。

3. PostgreSQLはINTERVALを使う

SELECT end_at - start_at AS elapsed FROM events;
SELECT EXTRACT(EPOCH FROM (end_at - start_at)) AS seconds FROM events;
SELECT AGE(end_at, start_at) FROM events;

PostgreSQLにも TIMESTAMPDIFF はありません。日時の減算で INTERVAL を得て、秒数が必要なら EXTRACT(EPOCH FROM (...)) を使います。年齢など年・月を考慮した暦上の差には AGE() が使えます。

EXTRACT(EPOCH ...) は経過秒を数値化する用途、AGE() は「1年2か月」のような暦要素を保つ用途です。画面表示用の年齢と課金時間の計算は要件が違うため、同じ式を流用しないようにします。

4. SQL ServerはDATEDIFF

SELECT DATEDIFF(day, '2026-07-01', '2026-07-22');

SQL Serverでは別名の DATEDIFF(unit, start, end) を使います。MySQLの関数名をそのまま探しても見つからない典型例です。SQL Serverの DATEDIFF は指定したdatepartの境界をまたいだ回数を返すため、厳密な経過時間を丸めた値とは異なる場合があります。

SELECT DATEDIFF(
  day,
  '2026-07-21 23:59:59',
  '2026-07-22 00:00:00'
);
-- 日付境界を1回またぐため1

経過は1秒でも、day の境界数は1です。大きな秒数などで戻り値範囲を超える可能性がある場合は、対応するバージョンで DATEDIFF_BIG の適否も確認してください。

5. SnowflakeとDatabricks / Spark SQL

-- Snowflake
SELECT TIMESTAMPDIFF(day, start_at, end_at) FROM events;
SELECT DATEDIFF(day, start_at, end_at) FROM events;
-- Databricks / Spark SQL（対応状況はバージョンで確認）
SELECT TIMESTAMPDIFF(DAY, start_at, end_at) FROM events;

Snowflakeでは TIMESTAMPDIFF(unit, start, end) が利用でき、DATEDIFF もあります。Databricks SQLやSpark SQLにもTIMESTAMPDIFF相当の機能がありますが、対応する単位や利用可能なバージョンに差があることがあります。利用中のランタイムのドキュメントで確認してください。

6. BigQueryはTIMESTAMP_DIFF

SELECT TIMESTAMP_DIFF(
  TIMESTAMP '2026-07-22 01:15:00+00',
  TIMESTAMP '2026-07-21 23:30:00+00',
  HOUR
);
-- 1

BigQueryではアンダースコア付きの TIMESTAMP_DIFF(end_timestamp, start_timestamp, granularity) を使います。MySQLの TIMESTAMPDIFF(unit, start, end) とは関数名だけでなく、単位の位置と日時引数の並びも異なります。

DATE_DIFF や DATETIME_DIFF など入力型に対応する別関数もあります。利用可能な粒度、境界の数え方、型の暗黙変換は変更される可能性もあるため、利用時点のBigQuery公式ドキュメントで確認してください。

7. DB製品・関数名・引数順の対応表

移植時は「開始から終了まで」という意味を固定し、各DBの構文へ割り当てます。関数名が似ていても、整数かINTERVALか、完全な単位数か境界数かが違うため、戻り値を利用するアプリ側の型も確認します。

8. 実務例: 会員登録からの経過日数

members.registered_at から基準日時までの完全な経過日数を求める例です。再現可能なテストにするため、CURRENT_TIMESTAMP ではなく固定した基準日時を使います。

MySQL / MariaDB

SELECT
  member_id,
  TIMESTAMPDIFF(
    DAY,
    registered_at,
    '2026-07-22 12:00:00'
  ) AS elapsed_days
FROM members;

PostgreSQL

SELECT
  member_id,
  FLOOR(
    EXTRACT(EPOCH FROM (
      TIMESTAMP '2026-07-22 12:00:00' - registered_at
    )) / 86400
  ) AS elapsed_days
FROM members;

PostgreSQLの例は経過秒を86400で割った「24時間単位」の日数です。暦上の日付が何回変わったかを求める式とは異なります。未来の登録日時では負数の丸め方も要件に影響するため、未来値を拒否するか、FLOOR、切り捨て、絶対値のどれを使うかを決めてください。

本番で現在時刻を使う場合は各DBの現在日時関数へ置き換えます。ただしトランザクション中に同じ値を返すか、文ごとに更新されるかは製品の仕様を確認し、集計全体で基準時刻を統一します。

9. タイムゾーンと夏時間をまたぐ差分

タイムゾーン付きの型同士で経過時間を求める場合は、同じ瞬間の尺度へ正規化してから比較します。PostgreSQLの TIMESTAMPTZ など瞬間を表す型なら、異なる表示オフセットでも実時間の差を計算できます。

-- PostgreSQL: 同じ瞬間なので差は0秒
SELECT EXTRACT(EPOCH FROM (
  TIMESTAMPTZ '2026-07-22 09:00:00+09'
  - TIMESTAMPTZ '2026-07-22 00:00:00+00'
)) AS seconds_diff;

一方、タイムゾーンなしの値は地域情報がないため、比較前に「どの地域の時刻か」を決めなければなりません。夏時間の切り替え日では壁時計上の2時間と実経過1時間が一致しない場合があります。24時間の経過を求めるのか、暦日や営業時間を数えるのかを先に定義してください。

型そのものの違いはTIMESTAMP型とDATE/DATETIME型の違いで詳しく説明しています。変換構文とタイムゾーンデータはDB製品やバージョンで異なるため、公式ドキュメントと夏時間境界のテストケースで検証します。

よくある間違い

• startとendを逆にする: MySQLでは符号が反転し、BigQueryでは日時引数の位置もMySQLと異なります。
• どのDBにもTIMESTAMPDIFFがあると思う: OracleとPostgreSQLでは減算や別関数を使います。
• 月を固定日数として計算する: 暦月の長さは一定ではなく、月末の扱いも関数ごとに確認が必要です。
• 境界数と経過時間を同一視する: SQL ServerのDATEDIFFはdatepart境界を数えます。
• INTERVALをそのまま整数として扱う: PostgreSQLやOracleでは必要な単位を明示して抽出します。
• 端数の丸めを決めない: 完全な単位数、切り捨て、FLOORは負数で結果が異なる場合があります。
• タイムゾーンなし日時を直接比較する: 元の地域が違うなら、比較前にそれぞれの地域を解釈します。
• 夏時間を常に24時間として扱う: 暦日の差と実経過秒の差を要件に応じて使い分けます。

English summary

MySQL and MariaDB use TIMESTAMPDIFF(unit, start, end); reversing the dates reverses the sign. Oracle uses subtraction and MONTHS_BETWEEN, while PostgreSQL uses interval subtraction, EXTRACT(EPOCH), or AGE. Choose elapsed seconds or calendar-aware intervals according to the business requirement.

SQL Server calls the function DATEDIFF and counts datepart boundaries. BigQuery uses TIMESTAMP_DIFF(end, start, granularity), with a different name and argument order from MySQL. Snowflake supports TIMESTAMPDIFF and DATEDIFF. For Databricks and Spark SQL, verify units and availability against the runtime documentation.

For practical calculations such as days since registration, define whether a day means 24 elapsed hours or a calendar-date boundary, and define rounding for negative values. Normalize timezone-aware values to comparable instants; timezone-free values need an explicit regional interpretation. Test daylight-saving transitions, month ends, leap days, and product-version behavior before relying on a migrated query.

関連ツール / Related tools

関連ガイド / Related guides