対応エンジン
| エンジン | 対応 | 備考 |
|---|---|---|
| TD（Presto） | ✅ | TD固有関数を組み合わせて実装 |
| Trino / Presto | ✅ | date_trunc・intervalを使う |
| Hive | ✅ | trunc・add_monthsを使う |
こんなときに使う
月次レポートや定期バッチで「先月分のデータを集計する」場面は非常に多いです。毎月日付をハードコードするのではなく動的に取得することで、スケジュール実行がそのまま使えます。
TDでの実装
SELECT
-- 今月1日
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) AS this_month_first,
-- 先月1日
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME() - 86400 * 31, 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) AS last_month_first,
-- 先月末日（= 今月1日の1秒前）
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) - 1 AS last_month_end,
-- 今月末日（= 来月1日の1秒前）
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME() + 86400 * 31, 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) - 1 AS this_month_end
ステップごとの解説
今月1日
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
)
TD_TIME_FORMATで'2024-01'を取り出し、'-01'をつなげて'2024-01-01'にしてからTD_TIME_PARSEでUNIXタイムに変換します。
先月1日
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME() - 86400 * 31, 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
)現在時刻から31日分の秒数を引くことで「確実に先月中のどこか」にずらしてから月初を取得します。28〜31日など月の日数に関係なく正確に先月1日が取れます。
先月末日・今月末日
-- 先月末日 = 今月1日の1秒前
TD_TIME_PARSE(...今月1日...) - 1
-- 今月末日 = 来月1日の1秒前
TD_TIME_PARSE(...来月1日...) - 1「翌月1日の1秒前 = 当月末日の23:59:59」という考え方です。月によって28日・30日・31日と変わる末日を、日数を気にせず正確に取れます。
Trino / Prestoでの実装
date_truncとintervalを組み合わせるとシンプルに書けます。
SELECT
-- 今月1日
date_trunc('month', now() AT TIME ZONE 'Asia/Tokyo') AS this_month_first,
-- 先月1日
date_trunc('month', now() AT TIME ZONE 'Asia/Tokyo' - interval '1' month) AS last_month_first,
-- 先月末日
date_trunc('month', now() AT TIME ZONE 'Asia/Tokyo') - interval '1' day AS last_month_end,
-- 今月末日
date_trunc('month', now() AT TIME ZONE 'Asia/Tokyo' + interval '1' month) - interval '1' day AS this_month_end
Hiveでの実装
SELECT
-- 今月1日
trunc(current_date, 'MM') AS this_month_first,
-- 先月1日
trunc(add_months(current_date, -1), 'MM') AS last_month_first,
-- 先月末日
date_sub(trunc(current_date, 'MM'), 1) AS last_month_end,
-- 今月末日
date_sub(trunc(add_months(current_date, 1), 'MM'), 1) AS this_month_end
エンジン別まとめ
| エンジン | 月初取得 | 月の加減算 | 末日取得 |
|---|---|---|---|
| TD | TD_TIME_FORMAT(..., 'yyyy-MM') || '-01' | 31日分の秒数を加減算 | 翌月1日 -1秒 |
| Trino / Presto | date_trunc('month', ...) | interval '1' month | 翌月1日 - interval '1' day |
| Hive | trunc(date, 'MM') | add_months | date_sub(翌月1日, 1) |
⚠️ よくあるミス
28日・30日を引いて先月1日を取得しようとする
-- ❌ 月によって日数が違うためズレる
-- 例：3月1日から28日引くと2月1日になるが、うるう年以外の2月は28日なので2月1日になってしまう
TD_SCHEDULED_TIME() - 86400 * 28
-- ✅ 31日引いて確実に先月中にずらす
TD_SCHEDULED_TIME() - 86400 * 31
末日をハードコードする
-- ❌ 月によって末日が違うためズレる
TD_TIME_PARSE('2024-01-31', 'Asia/Tokyo')
-- ✅ 翌月1日の1秒前として動的に取得する
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME() + 86400 * 31, 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) - 1
よくある応用：先月分データを丸ごと集計（TD）
WITH base AS (
SELECT
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME() - 86400 * 31, 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) AS last_month_first,
TD_TIME_PARSE(
TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM', 'Asia/Tokyo') || '-01',
'Asia/Tokyo'
) AS this_month_first
)
SELECT
COUNT(*) AS cnt
FROM your_table, base
WHERE TD_TIME_RANGE(time, last_month_first, this_month_first)
WITH句で先月1日・今月1日を変数のように定義しておくとクエリ本体がすっきりします。
まとめ
| 取得したいもの | 考え方 |
|---|---|
| 今月1日 | yyyy-MMを取り出して-01をつける |
| 先月1日 | 31日引いて月をずらしてから-01をつける |
| 先月末日 | 今月1日のUNIXタイム -1秒 |
| 今月末日 | 来月1日のUNIXタイム -1秒 |
Trinoはintervalが使えてシンプル | date_trunc + interval '1' month |