MySQL 訂閱制計費周期查詢：基於 UNIX 時間戳的月度付費日期精確計算與 SQL 實現

SaaS/訂閱業務常見需求：根據開始時間、計費間隔 (月/季/半年/年)、到期時間，精確計算每個自然月是否需要出帳。本文拆解時間數學邏輯，給出基於 UNIX 時間戳的高效 SQL 實現，避免日期函數陷阱。

一、 業務參數定義

二、 核心邏輯：月度邊界與周期對齊

關鍵觀察

• 付費日期 = ktime + N * jmonth 月 (N=0,1,2...)
• 實際出帳常提前幾天 (如 T-5 天) 留出支付窗口。
• 查詢目標月 (如 2009-02) 是否包含某次付費點。

數學轉換

1. 將 ktime、查詢月首尾轉為 年月數值 YYYYMM (整數)。
2. 計算 PERIOD_DIFF(query_ym, start_ym) = 兩月份之差 (月數)。
3. 判斷 diff % jmonth == 0 且在有效區間內 → 需付費。

三、 SQL 實現 (MySQL)

參數準備 (應用層注入)

SET @query_ym = 200902;                    -- 查詢月 YYYYMM
SET @month_start_ts = UNIX_TIMESTAMP('2009-02-01 00:00:00');
SET @month_end_ts   = UNIX_TIMESTAMP('2009-03-01 00:00:00');
SET @advance_sec = 5 * 86400;              -- 提前 5 天出帳

核心查詢

= @month_start_ts AND ktime < @month_end_ts)
      OR
      -- 情況 2: 首次付費在之前，且到期時間覆蓋本月(含提前量)，且周期對齊
      (ktime < @month_start_ts 
       AND dtime - @advance_sec > @month_end_ts
       AND PERIOD_DIFF(@query_ym, 
            DATE_FORMAT(FROM_UNIXTIME(ktime - @advance_sec), '%Y%m')
       ) % jmonth = 0)
  )
ORDER BY ktime DESC;

四、 邏輯逐條拆解

五、 關鍵函數說明

• FROM_UNIXTIME(ts) → DATETIME
• DATE_FORMAT(dt, '%Y%m') → 整數年月 (如 200902)
• PERIOD_DIFF(ym1, ym2) → ym1 - ym2 (月數)，支持跨年。
• 全部為 標量計算，可走索引 (需 ktime、dtime 索引)。

六、 索引建議

ALTER TABLE t_list ADD INDEX idx_ktime_dtime_jmonth (ktime, dtime, jmonth);

覆蓋 WHERE 條件列，避免回表。

七、 邊界測試用例

-- 測試數據
INSERT INTO t_list (ktime, dtime, jmonth) VALUES
(1228406400, 1259856000, 3); -- 2008-12-05 到 2009-12-04, 季付

-- 預期結果
-- 2008-12 (首月)          → 有
-- 2009-02 (差 2 月)       → 無
-- 2009-03 (差 3 月)       → 有
-- 2009-05 (差 5 月)       → 無
-- 2009-06 (差 6 月)       → 有

八、 總結

訂閱計費查詢核心 = 時間區間覆蓋判斷 + 周期對齊取模。用 PERIOD_DIFF + DATE_FORMAT 替代複雜日期運算，邏輯清晰、可索引、跨年安全。應用層只需注入查詢月首末時間戳與 YYYYMM 整數，SQL 保持靜態，極易維護與測試。