Treasure Data SQLで日付差分を計算する方法｜日数・時間数の出し方

SELECT
  -- 日数差
  (unix_time_b - unix_time_a) / 86400 AS diff_days,

  -- 時間差
  (unix_time_b - unix_time_a) / 3600 AS diff_hours,

  -- 分差
  (unix_time_b - unix_time_a) / 60 AS diff_minutes
FROM your_table

SELECT
  FLOOR((unix_time_b - unix_time_a) / 86400) AS diff_days
FROM your_table
-- → 31

SELECT
  user_id,
  register_time,
  FLOOR((TD_SCHEDULED_TIME() - register_time) / 86400) AS days_since_register
FROM user_table

SELECT
  -- 日数差
  date_diff('day', timestamp_a, timestamp_b) AS diff_days,

  -- 時間差
  date_diff('hour', timestamp_a, timestamp_b) AS diff_hours,

  -- 分差
  date_diff('minute', timestamp_a, timestamp_b) AS diff_minutes,

  -- 月差
  date_diff('month', timestamp_a, timestamp_b) AS diff_months
FROM your_table

SELECT
  date_diff(
    'day',
    from_unixtime(unix_time_a) AT TIME ZONE 'Asia/Tokyo',
    from_unixtime(unix_time_b) AT TIME ZONE 'Asia/Tokyo'
  ) AS diff_days
FROM your_table

SELECT
  -- 日数差（date型またはyyyy-MM-dd形式の文字列を渡す）
  datediff(date_b, date_a) AS diff_days,

  -- 時間差（UNIXタイムから計算）
  (unix_time_b - unix_time_a) / 3600 AS diff_hours
FROM your_table

-- ❌ 文字列同士は引き算できない
'2024-02-01' - '2024-01-01'

-- ✅ TD_TIME_PARSEでUNIXタイムに変換してから計算
(
  TD_TIME_PARSE('2024-02-01', 'Asia/Tokyo')
  - TD_TIME_PARSE('2024-01-01', 'Asia/Tokyo')
) / 86400
-- → 31

-- ❌ 順序が逆だと負の値になる
date_diff('day', timestamp_b, timestamp_a)
-- → -31

-- ✅ 古い日付を第2引数・新しい日付を第3引数に
date_diff('day', timestamp_a, timestamp_b)
-- → 31

-- ❌ 時刻がずれていると日数が小数になる
-- unix_time_a = 2024-01-01 00:00:00
-- unix_time_b = 2024-02-01 12:00:00
(unix_time_b - unix_time_a) / 86400
-- → 31.5

-- ✅ FLOOR・CEILで整数に丸める
FLOOR((unix_time_b - unix_time_a) / 86400)
-- → 31

SELECT
  user_id,
  first_purchase_time,
  second_purchase_time,
  FLOOR(
    (second_purchase_time - first_purchase_time) / 86400
  ) AS days_to_repurchase
FROM purchase_table