【Oracle】今日の 0:00（当日開始時刻）を取得する方法｜TRUNC(SYSDATE)・日付範囲フィルタ・昨日/今週/今月パターンまで解説

-- 今日の 0:00:00 を取得
SELECT TRUNC(SYSDATE) AS today_start FROM DUAL;
-- 結果例: 2026-03-28 00:00:00

-- 確認: 時刻が 00:00:00 であることを TO_CHAR で表示
SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') AS today_start
FROM DUAL;
-- 結果: 2026-03-28 00:00:00

-- 今日の 23:59:59（当日の最終秒）
SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59' HOUR TO SECOND AS today_end
FROM DUAL;

-- 明日の 0:00:00（翌日の開始）
SELECT TRUNC(SYSDATE) + 1 AS tomorrow_start FROM DUAL;

-- 昨日の 0:00:00
SELECT TRUNC(SYSDATE) - 1 AS yesterday_start FROM DUAL;

-- 今日の 0:00 から 1 秒前 = 昨日の 23:59:59
SELECT TRUNC(SYSDATE) - INTERVAL '1' SECOND AS yesterday_end FROM DUAL;

-- 推奨: >= 今日 0:00 AND < 明日 0:00
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE)
  AND order_date <  TRUNC(SYSDATE) + 1;

-- NG: BETWEEN で 23:59:59 を使うと秒未満が漏れる可能性
-- WHERE order_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 1 - 1/86400

SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 1
  AND order_date <  TRUNC(SYSDATE);

-- IW: ISO 週（月曜始まり）
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE, 'IW')
  AND order_date <  TRUNC(SYSDATE, 'IW') + 7;

SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE, 'MM')
  AND order_date <  TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM');

-- 過去 7 日間（今日含む）
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 7
  AND order_date <  TRUNC(SYSDATE) + 1;

-- 過去 30 日間（今日含む）
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 30
  AND order_date <  TRUNC(SYSDATE) + 1;

SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE, 'YYYY')
  AND order_date <  TRUNC(ADD_MONTHS(SYSDATE, 12), 'YYYY');

-- TIMESTAMP 型で今日の 0:00 を取得
SELECT CAST(TRUNC(SYSDATE) AS TIMESTAMP) AS today_ts FROM DUAL;
-- 結果: 2026-03-28 00:00:00.000000

-- SYSTIMESTAMP から時刻を切り捨て
SELECT TRUNC(SYSTIMESTAMP) AS today_ts FROM DUAL;
-- 結果: DATE 型（00:00:00）が返る

-- TIMESTAMP WITH TIME ZONE で今日の 0:00
SELECT CAST(TRUNC(CURRENT_DATE) AS TIMESTAMP WITH TIME ZONE) AS today_tz
FROM DUAL;

-- DB サーバーのタイムゾーン確認
SELECT DBTIMEZONE FROM DUAL;

-- セッションのタイムゾーン確認
SELECT SESSIONTIMEZONE FROM DUAL;

-- セッションタイムゾーンを変更して確認
ALTER SESSION SET TIME_ZONE = '+09:00';  -- JST
SELECT TRUNC(CURRENT_DATE) AS today_jst FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-05:00';  -- EST
SELECT TRUNC(CURRENT_DATE) AS today_est FROM DUAL;
-- DB サーバーが JST の場合、EST だと日付が 1 日ずれることがある

-- NG: SYSDATE は時刻を含むため、TRUNC と一致しない
SELECT * FROM orders WHERE order_date = TRUNC(SYSDATE);
-- → 0:00:00 ちょうどに登録されたデータしか取れない

-- OK: 範囲指定で「今日」のデータを取得
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE)
  AND order_date <  TRUNC(SYSDATE) + 1;

-- NG: 文字列比較（インデックスが効かない + 遅い）
SELECT * FROM orders
WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD');

-- OK: DATE 同士の範囲比較（インデックスが効く）
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE)
  AND order_date <  TRUNC(SYSDATE) + 1;

-- 毎朝実行するバッチ: 昨日のデータを集計
INSERT INTO daily_summary (summary_date, total_orders, total_amount)
SELECT
    TRUNC(SYSDATE) - 1 AS summary_date,
    COUNT(*),
    SUM(amount)
FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 1
  AND order_date <  TRUNC(SYSDATE);

SELECT * FROM employees
WHERE updated_at >= TRUNC(SYSDATE)
ORDER BY updated_at DESC;

SELECT
    TRUNC(order_date)        AS order_day,
    COUNT(*)                  AS order_count,
    SUM(amount)              AS total_amount
FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 7
  AND order_date <  TRUNC(SYSDATE) + 1
GROUP BY TRUNC(order_date)
ORDER BY order_day;

-- 今日の 9:00
SELECT TRUNC(SYSDATE) + 9/24 AS today_9am FROM DUAL;

-- 今日の 18:00
SELECT TRUNC(SYSDATE) + 18/24 AS today_6pm FROM DUAL;

-- INTERVAL を使う方法
SELECT TRUNC(SYSDATE) + INTERVAL '9' HOUR AS today_9am FROM DUAL;
SELECT TRUNC(SYSDATE) + INTERVAL '18' HOUR AS today_6pm FROM DUAL;

-- 営業時間内（9:00〜18:00）のデータ
SELECT * FROM access_log
WHERE log_time >= TRUNC(SYSDATE) + INTERVAL '9' HOUR
  AND log_time <  TRUNC(SYSDATE) + INTERVAL '18' HOUR;