【SQL】日付の比較完全ガイド｜比較演算子・相対期間・DATETIME落とし穴・SARGable・タイムゾーン・実務パターンまで解説

-- orders テーブル（注文）
-- id | customer | amount | status   | ordered_at              | shipped_at
--  1 | 田中     |  12000 | shipped  | 2024-01-15 09:30:00     | 2024-01-17 14:00:00
--  2 | 鈴木     |   3500 | pending  | 2024-02-01 15:45:00     | NULL
--  3 | 高橋     |  85000 | shipped  | 2024-02-20 11:00:00     | 2024-02-22 10:30:00
--  4 | 田中     |   2800 | shipped  | 2024-03-05 08:00:00     | 2024-03-06 16:00:00
--  5 | 伊藤     |  45000 | pending  | 2024-03-10 17:30:00     | NULL
--  6 | 渡辺     |   9500 | canceled | 2024-04-01 10:00:00     | NULL

-- memberships テーブル（会員情報）
-- id | name  | started_at  | expired_at
--  1 | 田中  | 2023-04-01  | 2025-03-31
--  2 | 鈴木  | 2024-01-15  | 2025-01-14
--  3 | 高橋  | 2022-10-01  | 2024-09-30
--  4 | 伊藤  | 2024-06-01  | NULL  （無期限）

-- 2024-02-01 以降の注文
SELECT id, customer, amount, ordered_at
FROM orders
WHERE ordered_at >= '2024-02-01'
ORDER BY ordered_at;

-- 2024年1月〜3月の注文（BETWEEN で範囲指定）
SELECT id, customer, amount, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-03-31 23:59:59';
-- ↑ DATETIME列の場合は終端に時刻を含める（後述）

-- 特定日に発送された注文（DATE関数で時刻を除去）
SELECT id, customer, shipped_at
FROM orders
WHERE DATE(shipped_at) = '2024-01-17';

-- 今日以降の注文（MySQL / PostgreSQL）
SELECT * FROM orders WHERE ordered_at >= CURDATE();

-- 今日以降の注文（すべての RDBMS で動作する標準構文）
SELECT * FROM orders WHERE ordered_at >= CURRENT_DATE;

-- 現在時刻以前（有効期限が切れていないメンバー）
SELECT * FROM memberships WHERE expired_at >= NOW();
-- または
SELECT * FROM memberships WHERE expired_at >= CURRENT_TIMESTAMP;

-- 今日注文されたデータ（MySQL）
SELECT * FROM orders WHERE DATE(ordered_at) = CURDATE();
-- 今日注文されたデータ（PostgreSQL）
SELECT * FROM orders WHERE ordered_at::date = CURRENT_DATE;

-- 直近 30 日間の注文
SELECT id, customer, amount, ordered_at
FROM orders
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND ordered_at <  NOW();

-- 直近 7 日間
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 直近 3 ヶ月
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

-- 直近 1 年
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- PostgreSQL
WHERE ordered_at >= NOW() - INTERVAL '30 days'

-- SQL Server
WHERE ordered_at >= DATEADD(DAY, -30, GETDATE())

-- Oracle
WHERE ordered_at >= SYSDATE - 30  -- 30日前（数値で日数を引ける）
-- Oracle（月単位）
WHERE ordered_at >= ADD_MONTHS(SYSDATE, -3)  -- 3ヶ月前

-- ===== 今月のデータ =====

-- MySQL: 今月の最初の日〜最後の日
SELECT * FROM orders
WHERE ordered_at >= DATE_FORMAT(NOW(), '%Y-%m-01')  -- 月初
  AND ordered_at <  DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH);  -- 翌月初

-- MySQL: YEAR / MONTH 関数を使う方法（インデックスに注意 → 後述）
SELECT * FROM orders
WHERE YEAR(ordered_at) = YEAR(NOW())
  AND MONTH(ordered_at) = MONTH(NOW());

-- PostgreSQL: 今月
SELECT * FROM orders
WHERE ordered_at >= DATE_TRUNC('month', NOW())
  AND ordered_at <  DATE_TRUNC('month', NOW()) + INTERVAL '1 month';

-- SQL Server: 今月
SELECT * FROM orders
WHERE ordered_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
  AND ordered_at <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0);

-- ===== 今年のデータ =====
-- MySQL: インデックスに優しい書き方
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01';

-- MySQL: YEAR 関数（インデックス非使用になることがある）
SELECT * FROM orders WHERE YEAR(ordered_at) = 2024;

-- NG: DATETIME列に = で日付のみを比較すると 0件になる
-- ordered_at = '2024-01-15 00:00:00' と解釈される
SELECT * FROM orders WHERE ordered_at = '2024-01-15';
-- → id=1（09:30:00）はマッチしない → 0件

-- OK: 翌日の 00:00:00 未満で範囲指定（最も効率的・インデックスが効く）
SELECT * FROM orders
WHERE ordered_at >= '2024-01-15'
  AND ordered_at <  '2024-01-16';
-- → id=1（2024-01-15 09:30:00）✓

-- OK: DATE() 関数で日付部分を切り出して比較（インデックスが効きにくい）
SELECT * FROM orders WHERE DATE(ordered_at) = '2024-01-15';

-- OK: BETWEEN（終端に 23:59:59 を含める）
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-01-15' AND '2024-01-15 23:59:59';
-- ↑ 23:59:59.999 などのマイクロ秒が含まれると漏れる場合あり
-- 「>= 開始日 AND < 翌日」のパターンがより安全

-- 注文から 3 日以内に発送されたか
SELECT id, customer, ordered_at, shipped_at,
       DATEDIFF(shipped_at, ordered_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL
  AND DATEDIFF(shipped_at, ordered_at) <= 3;

-- 有効期限まで 30 日以内の会員
SELECT id, name, expired_at,
       DATEDIFF(expired_at, CURDATE()) AS days_left
FROM memberships
WHERE expired_at IS NOT NULL
  AND DATEDIFF(expired_at, CURDATE()) BETWEEN 0 AND 30;

-- RDBMS別のDATEDIFF（引数の順序に注意）
-- MySQL: DATEDIFF(終了日, 開始日)
SELECT DATEDIFF('2024-03-01', '2024-01-01');  -- 60

-- SQL Server: DATEDIFF(単位, 開始日, 終了日)
SELECT DATEDIFF(DAY, '2024-01-01', '2024-03-01');  -- 60

-- PostgreSQL: '2024-03-01'::date - '2024-01-01'::date
SELECT '2024-03-01'::date - '2024-01-01'::date;  -- 60

-- Oracle: 日付どうしを引き算（日数の差を返す）
SELECT TO_DATE('2024-03-01','YYYY-MM-DD') - TO_DATE('2024-01-01','YYYY-MM-DD') FROM dual;  -- 60

-- NG: YEAR() 関数を列に適用（インデックス非使用）
SELECT * FROM orders WHERE YEAR(ordered_at) = 2024;

-- OK: 範囲比較に書き直す（インデックスが効く）
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01';

-- NG: 列側で計算（インデックス非使用）
SELECT * FROM orders WHERE DATEDIFF(NOW(), ordered_at) <= 30;

-- OK: 定数側で計算（インデックスが効く）
SELECT * FROM orders
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- EXPLAIN で実行計画を確認
EXPLAIN SELECT * FROM orders
WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01';
-- type が 'range' であればインデックスが使われている

-- shipped_at が NULL の行は除外される（UNKNOWN のため）
SELECT id, customer FROM orders WHERE shipped_at <= '2024-12-31';
-- → id=2, 5, 6（NULL）は返らない

-- NULL を含めたい場合は IS NULL を OR で追加
SELECT id, customer FROM orders
WHERE shipped_at <= '2024-12-31' OR shipped_at IS NULL;

-- expired_at が NULL（無期限）の会員も有効として取得
SELECT id, name FROM memberships
WHERE expired_at >= CURDATE()  -- 有効期限内
   OR expired_at IS NULL;       -- または無期限

-- COALESCE で NULL を遠い未来の日付に置換
SELECT id, name FROM memberships
WHERE COALESCE(expired_at, '9999-12-31') >= CURDATE();

-- MySQL: セッションのタイムゾーンを確認・変更
SELECT @@time_zone, @@system_time_zone;
SET time_zone = '+09:00';  -- JST に設定

-- UTC で保存されているデータを JST で比較（MySQL）
SELECT * FROM orders
WHERE CONVERT_TZ(ordered_at, '+00:00', '+09:00') >= '2024-01-01 00:00:00';

-- UTC で保存されているデータを JST で比較（PostgreSQL）
-- AT TIME ZONE で変換
SELECT * FROM orders
WHERE (ordered_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Tokyo' >= '2024-01-01 00:00:00';

-- JST の「2024-01-01 00:00:00」は UTC では「2023-12-31 15:00:00」
-- UTC で保存しているなら比較値を UTC に変換してから使う
SELECT * FROM orders
WHERE ordered_at >= '2023-12-31 15:00:00';  -- UTC で 2024-01-01 00:00:00 JST

-- 推奨: アプリ側でタイムゾーン変換し、UTC のまま WHERE に渡す
-- → コードが明確で移植性も高い

-- 現在有効なメンバーシップ（今日が開始〜終了の範囲内）
SELECT id, name, started_at, expired_at
FROM memberships
WHERE started_at <= CURDATE()
  AND (expired_at >= CURDATE() OR expired_at IS NULL);

-- 発送が遅延している注文（注文から 5 日後も未発送）
SELECT id, customer, ordered_at, shipped_at
FROM orders
WHERE shipped_at IS NULL
  AND ordered_at < DATE_SUB(CURDATE(), INTERVAL 5 DAY);

-- 2 つの期間が重複するかチェック（期間 A: a_start〜a_end、期間 B: b_start〜b_end）
-- 重複の条件: A の開始 <= B の終了 AND A の終了 >= B の開始
SELECT *
FROM schedule AS a
JOIN schedule AS b ON a.id <> b.id
WHERE a.start_date <= b.end_date
  AND a.end_date   >= b.start_date;

-- 列間の大小比較（started_at < expired_at のチェック）
SELECT id, name, started_at, expired_at
FROM memberships
WHERE started_at >= expired_at;  -- データ不整合の検出

-- 有効期限が 30 日以内に切れる会員（更新案内対象）
SELECT id, name, expired_at,
       DATEDIFF(expired_at, CURDATE()) AS days_left
FROM memberships
WHERE expired_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
ORDER BY expired_at;

-- 今月中に有効期限が切れる会員
SELECT id, name, expired_at
FROM memberships
WHERE expired_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND expired_at <  DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH);

-- 90 日以上注文していない顧客（休眠顧客）
SELECT customer, MAX(ordered_at) AS last_order
FROM orders
GROUP BY customer
HAVING MAX(ordered_at) < DATE_SUB(CURDATE(), INTERVAL 90 DAY);

-- 前年同月との比較（今月の売上）
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS month,
    SUM(amount) AS total
FROM orders
WHERE ordered_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01')
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY month;

-- 日別の注文件数（今月分）
SELECT
    DATE(ordered_at) AS order_date,
    COUNT(*)         AS cnt,
    SUM(amount)      AS daily_total
FROM orders
WHERE ordered_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND ordered_at <  DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
GROUP BY DATE(ordered_at)
ORDER BY order_date;