您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

MySQL 指定日期時間範圍查詢：Unix 時間戳與 mktime 實戰技巧

編輯：PHP 綜合知識

資料庫時間欄位若採用 INT(11) 儲存 Unix 時間戳（秒數），查詢「最近 N 天/月/小時」只需用 PHP mktime() 或 strtotime() 算出邊界時間戳，再以 >/< 比較，效能極佳且跨時區無憂。

核心原理

Unix 時間戳 = 從 1970-01-01 00:00:00 UTC 到現在的秒數，與時區無關
資料庫欄位 dtime INT(11) NOT NULL DEFAULT 0 儲存 time() 或 mktime() 產生的整數
查詢條件：WHERE dtime > $startTimestamp AND dtime < $endTimestamp

PHP 計算常用時間邊界

<?php
// 當前時間戳
$now = time();

// --- 相對時間（推薦用 strtotime，語意清晰、自動處理月份天數、閏年） ---
$oneHourAgo      = strtotime('-1 hour');
$oneDayAgo       = strtotime('-1 day');
$sevenDaysAgo    = strtotime('-7 days');
$oneMonthAgo     = strtotime('-1 month');
$threeMonthsAgo  = strtotime('-3 months');
$oneYearAgo      = strtotime('-1 year');

// --- 固定時間點（當天 00:00:00 / 23:59:59） ---
$todayStart      = strtotime('today');           // 今天 00:00:00
$todayEnd        = strtotime('tomorrow') - 1;    // 今天 23:59:59
$yesterdayStart  = strtotime('yesterday');
$yesterdayEnd    = strtotime('today') - 1;

// --- 本週/上週（週一為起始） ---
$weekStart       = strtotime('monday this week');
$weekEnd         = strtotime('sunday this week') + 86399;
$lastWeekStart   = strtotime('monday last week');
$lastWeekEnd     = strtotime('sunday last week') + 86399;

// --- 本月/上月 ---
$monthStart      = strtotime('first day of this month');
$monthEnd        = strtotime('first day of next month') - 1;
$lastMonthStart  = strtotime('first day of last month');
$lastMonthEnd    = strtotime('first day of this month') - 1;

// --- 自訂日期字串轉時間戳 ---
$custom = strtotime('2024-01-15 14:30:00'); // 依當時區解析，建議明確指定 UTC
$customUtc = strtotime('2024-01-15 14:30:00 UTC');

SQL 查詢範例

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 最近 7 天
$stmt = $pdo->prepare('SELECT * FROM `testtable` WHERE `dtime` > :start AND `dtime` <= :end');
$stmt->execute([
    'start' => strtotime('-7 days'),
    'end'   => time(),
]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 本月資料
$stmt = $pdo->prepare('SELECT * FROM `testtable` WHERE `dtime` >= :start AND `dtime` < :end');
$stmt->execute([
    'start' => strtotime('first day of this month'),
    'end'   => strtotime('first day of next month'),
]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 任意區間
$start = strtotime('2024-01-01 00:00:00');
$end   = strtotime('2024-01-31 23:59:59');
$stmt = $pdo->prepare('SELECT * FROM `testtable` WHERE `dtime` BETWEEN :start AND :end');
$stmt->execute(compact('start','end'));

進階技巧

1. 直接在 SQL 計算（不依賴 PHP）

-- 最近 7 天（MySQL 5.6+）
SELECT * FROM `testtable`
WHERE `dtime` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

-- 本月
SELECT * FROM `testtable`
WHERE `dtime` >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))
  AND `dtime` < UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01'));

注意：NOW() 回傳伺服器時區時間，若資料庫時區與應用時區不一致會有偏差。建議資料庫、PHP、應用層統一設為 UTC，消除所有时区烦恼。

2. 索引優化

dtime 欄位務必建立索引：ALTER TABLE testtable ADD INDEX idx_dtime (dtime);
避免在欄位上使用函數：WHERE FROM_UNIXTIME(dtime) > '2024-01-01' 會導致全表掃描，改用 WHERE dtime > UNIX_TIMESTAMP('2024-01-01')

3. 儲存型別選擇對照

型別	儲存內容	時區感知	適用場景
`INT(11)` / `BIGINT`	Unix 時間戳 (秒/毫秒)	無 (UTC 絕對值)	高效能查詢、跨時區、排程、日誌
`TIMESTAMP`	UTC 秒數，寫入/讀取自動轉換連線時區	有	記錄建立/更新時間、自動維護
`DATETIME`	YYYY-MM-DD HH:MM:SS，無時區資訊	無	業務時間（預約、生日）、需保留原始輸入
`DATETIME(3)` / `TIMESTAMP(3)`	毫秒精度	同上	高精度需求（交易、監控）

避坑指南

不要用 date('h') 等格式字串拼 SQL：原文範例 mktime(date('h'),...) 易在 12 小時制/前導零/字串轉型上出錯，改用 strtotime() 或 DateTime 物件
32 位系統 2038 年問題：INT(11) 最大 2147483647 (2038-01-19)，長期專案請用 BIGINT 或 DATETIME
夏令時間邊界：strtotime('-1 month') 在 3/31 → 2/28/29 正確，但「同一天數」邏輯需謹慎（如 1/31 -1 month = 12/31）
時區不一致：PHP date_default_timezone_set('UTC')、MySQL SET time_zone = '+00:00'、應用層統一 UTC，儲存/查詢零困擾