Redshift でタイムスタンプを月曜日に丸める方法など

Redshift
🍪この記事の内容:
  • RedShift でタイムスタンプを直前の月曜日に丸める。
  • RedShift でタイムスタンプを基準日から N 日ごとの日に丸める。
ログを週ごとや4週ごとに集約したいときなど、タイムスタンプを丸めたいことがある。Presto には日付を丸める関数 date_trunc があるが、Redshift にはないので、Redshift でタイムスタンプを丸める方法の例を記す。

参考文献

  1. TO_TIMESTAMP 関数 - Amazon Redshift, , 2025年9月25日参照.
  2. EXTRACT 関数 - Amazon Redshift, , 2025年9月25日参照.
  3. ISO 8601 - Wikipedia#年と週と曜日, , 2025年9月25日参照.
  4. AT TIME ZONE 関数 - Amazon Redshift, , 2025年9月25日参照.
  5. DATEDIFF 関数 - Amazon Redshift, , 2025年9月25日参照.

タイムスタンプを直前の月曜日に丸める

Redshift の DOW (曜日番号) は日曜日が 0, ..., 土曜日が 6 なので、日曜日以外は DOW - 1 日前の日付をとれば月曜日になる。日曜日は 6 日前の日付をとれば月曜日になる。よって、すべての日について (DOW + 6) % 7 日前をとれば月曜日になる。
  • なお、ISODOW (ISO 8601 規格の曜日) なら月曜日が 1, ..., 日曜日が 7 なので常に ISODOW - 1 日前をとれば月曜日になるが、Redshift は ISODOW に対応していない。

WITH test_data AS (
  -- テストデータ: ここでは UTC タイムゾーン付きタイムスタンプ TIMESTAMPTZ 型とする
  SELECT TO_TIMESTAMP(v::TEXT, 'YYYYMMDD HH24:MI:SS.MS') AS ts_utc
  FROM (
    SELECT ARRAY(
      '20250901 02:00:00.000',  -- 日本でも月曜
      '20250902 02:00:00.000',  -- 日本でも火曜
      '20250907 02:00:00.000',  -- 日本でも日曜
      '20250907 22:00:00.000',  -- 日本では月曜 (イギリスではまだ日曜)
      '20250908 02:00:00.000'  -- 日本でも月曜
    ) AS v
  ) AS g, g.v AS v
)
SELECT
  ts_utc,
  TO_CHAR(  -- (DOW + 6) % 7 日前をとれば直前の月曜日に丸めることができる
    ts_utc - INTERVAL '1 day' * ((EXTRACT(DOW FROM ts_utc) + 6) % 7),
    'YYYY-MM-DD'
  ) AS week_utc
FROM test_data


ts_utc,                 week_utc
2025-09-01 02:00:00+00, 2025-09-01
2025-09-02 02:00:00+00, 2025-09-01
2025-09-07 02:00:00+00, 2025-09-01
2025-09-07 22:00:00+00, 2025-09-01
2025-09-08 02:00:00+00, 2025-09-08
もしデータベースに格納されているタイムスタンプが目的のタイムゾーンの時刻ではない場合は、DOW をとる前に目的のタイムゾーンの時刻にする必要があることに注意 (以下: 日本時間にするとテストデータの4行目は丸め先が変わる)。
  • AT TIME ZONE 関数はタイムゾーン付きタイムスタンプ TIMESTAMPTZ 型をタイムゾーンなしタイムスタンプ TIMESTAMP 型にするが、どちらも EXTRACT 関数で曜日番号を抽出できる。

SELECT
  -- もしデータベースが UTC だが日本における直前の月曜日をとりたい場合は JST に変換する
  ts_utc AT TIME ZONE 'JST' AS ts_jst,
  TO_CHAR(
    ts_jst - INTERVAL '1 day' * ((EXTRACT(DOW FROM ts_jst) + 6) % 7),
    'YYYY-MM-DD'
  ) AS week_jst
FROM test_data


ts_jst,              week_jst
2025-09-01 11:00:00, 2025-09-01
2025-09-02 11:00:00, 2025-09-01
2025-09-07 11:00:00, 2025-09-01
2025-09-08 07:00:00, 2025-09-08
2025-09-08 11:00:00, 2025-09-08

タイムスタンプを基準日から N 日ごとの日に丸める

より一般に、基準日から N 日ごとにデータを区切るためにタイムスタンプを丸めたいときは、DATEDIFF 関数 (日付の差を返す) を利用して、DATEDIFF % N 日前の日付をとればよい。
  • 以下では基準日をタイムゾーンなしタイムスタンプ TIMESTAMP 型として与え、DATEDIFF をとるときはデータ側も TIMESTAMP 型にキャストしている。

WITH test_data AS (
  -- テストデータ: ここでは UTC タイムゾーン付きタイムスタンプ TIMESTAMPTZ 型とする
  SELECT TO_TIMESTAMP(v::TEXT, 'YYYYMMDD HH24:MI:SS.MS') AS ts_utc
  FROM (
    SELECT ARRAY(
      '20250901 02:00:00.000',  -- 日本でも9月の1週目
      '20250902 02:00:00.000',  -- 日本でも9月の1週目
      '20250907 02:00:00.000',  -- 日本でも9月の1週目
      '20250907 22:00:00.000',  -- 日本では9月の2週目 (イギリスではまだ1週目)
      '20250915 02:00:00.000',  -- 日本でも9月の3週目
      '20250922 02:00:00.000',  -- 日本でも9月の4週目
      '20250929 02:00:00.000'  -- 日本でも9月の5週目
    ) AS v
  ) AS g, g.v AS v
),
configs AS (
  SELECT
    '20250901'::TIMESTAMP AS kijun  -- 基準日
)
SELECT
  ts_utc,
  DATEDIFF(days, (SELECT kijun FROM configs), ts_utc::TIMESTAMP) AS diff,
  TO_CHAR(ts_utc - INTERVAL '1 day' * (diff % 7), 'YYYY-MM-DD') AS week,
  TO_CHAR(ts_utc - INTERVAL '1 day' * (diff % 28), 'YYYY-MM-DD') AS week4
FROM test_data


ts_utc,                 diff, week,       week4
2025-09-01 02:00:00+00, 0,    2025-09-01, 2025-09-01
2025-09-02 02:00:00+00, 1,    2025-09-01, 2025-09-01
2025-09-07 02:00:00+00, 6,    2025-09-01, 2025-09-01
2025-09-07 22:00:00+00, 6,    2025-09-01, 2025-09-01
2025-09-15 02:00:00+00, 14,   2025-09-15, 2025-09-01
2025-09-22 02:00:00+00, 21,   2025-09-22, 2025-09-01
2025-09-29 02:00:00+00, 28,   2025-09-29, 2025-09-29
もしデータベースに格納されているタイムスタンプが目的のタイムゾーンの時刻ではない場合は、DATEDIFF をとる前に目的のタイムゾーンの時刻にする必要があることに注意 (以下: 日本時間にするとテストデータの4行目は丸め先が変わる)。 

SELECT
  -- もしデータベースが UTC だが日本における基準日から N 日ごとの日に丸めたい場合は変換する
  ts AT TIME ZONE 'JST' AS ts_jst,  -- TIMESTAMP 型
  DATEDIFF(days, (SELECT kijun FROM configs), ts_jst) AS diff,
  TO_CHAR(ts_jst - INTERVAL '1 day' * (diff % 7), 'YYYY-MM-DD') AS week,
  TO_CHAR(ts_jst - INTERVAL '1 day' * (diff % 28), 'YYYY-MM-DD') AS week4
FROM test_data


ts_jst,              diff, week,       week4
2025-09-01 11:00:00, 0,    2025-09-01, 2025-09-01
2025-09-02 11:00:00, 1,    2025-09-01, 2025-09-01
2025-09-07 11:00:00, 6,    2025-09-01, 2025-09-01
2025-09-08 07:00:00, 7,    2025-09-08, 2025-09-01
2025-09-15 11:00:00, 14,   2025-09-15, 2025-09-01
2025-09-22 11:00:00, 21,   2025-09-22, 2025-09-01
2025-09-29 11:00:00, 28,   2025-09-29, 2025-09-29