MySQL 核心時間戳函式

1. UNIX_TIMESTAMP() — 日期轉時間戳

-- 當前時間戳
SELECT UNIX_TIMESTAMP(); 
-- 結果：1436775578

-- 指定日期時間轉換
SELECT UNIX_TIMESTAMP('2015-07-13 16:20:20');
-- 結果：1436775620

-- 日期字串格式彈性支援
SELECT UNIX_TIMESTAMP('2015-07-13');        -- 當天 00:00:00
SELECT UNIX_TIMESTAMP('20150713162020');    -- 緊湊格式

2. FROM_UNIXTIME() — 時間戳轉日期

-- 預設格式：YYYY-MM-DD HH:MM:SS
SELECT FROM_UNIXTIME(1436775620);
-- 結果：2015-07-13 16:20:20

-- 自訂格式輸出
SELECT FROM_UNIXTIME(1436775620, '%Y-%m-%d');        -- 2015-07-13
SELECT FROM_UNIXTIME(1436775620, '%Y%m%d %H%i%s');   -- 20150713 162020
SELECT FROM_UNIXTIME(1436775620, '%W, %M %D, %Y');  -- Monday, July 13th, 2015

常用格式化符號速查

符號	意義	範例
%Y	四位數年份	2015
%y	兩位數年份	15
%m	月份 (01-12)	07
%d	日期 (01-31)	13
%H	小時 24制 (00-23)	16
%h	小時 12制 (01-12)	04
%i	分鐘 (00-59)	20
%s	秒數 (00-59)	20

MySQL 時間戳型別限制：2038 年問題

TIMESTAMP 型別儲存範圍：1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC（32 位元有號整數上限）。

SELECT FROM_UNIXTIME(0);           -- 1970-01-01 08:00:00 (含時區偏移)
SELECT FROM_UNIXTIME(2147483647);  -- 2038-01-19 11:14:07 (CST 時區)
-- 超過上限回傳 NULL

解決方案：使用 DATETIME（範圍 1000-9999 年）或 BIGINT 存儲毫秒級時間戳。

PHP 時間戳互操作

PHP → MySQL：日期存入資料庫

<?php
// 當前時間戳
$ts = time(); // 秒級
$ts_ms = microtime(true); // 毫秒級浮點數

// 格式化為 MySQL DATETIME
$datetime = date('Y-m-d H:i:s', $ts); // 2015-07-13 16:20:20

// 字串解析為時間戳
$ts = strtotime('2015-07-13 16:20:20'); // 1436775620
$ts = strtotime('+1 week');             // 相對時間
$ts = mktime(16, 20, 20, 7, 13, 2015);  // 組裝時間

MySQL → PHP：讀取後格式化顯示

<?php
// 假設從資料庫讀到 $row['created_at'] = '2015-07-13 16:20:20'
$ts = strtotime($row['created_at']);
echo date('Y/m/d H:i', $ts); // 2015/07/13 16:20

// 或直接用 DateTime 物件（推薦）
$dt = new DateTime($row['created_at']);
echo $dt->format('Y-m-d H:i:s');
$dt->modify('+1 day'); // 日期運算

時區陷阱與最佳實踐

1. 資料庫層：建議將 time_zone 設為 UTC，儲存統一 UTC 時間。
2. 應用層：PHP 設定 date_default_timezone_set('Asia/Taipei') 或使用者偏好時區。
3. 傳輸層：API 回傳 ISO 8601 格式（2015-07-13T16:20:20+08:00），前端再按使用者時區顯示。

自訂格式化函式（PHP）

function strToTimestamp($datetime) {
    // 驗證格式：YYYY-MM-DD HH:MM:SS
    if (!preg_match('/^\d{4}-\d{1,2}-\d{1,2} \d{2}:\d{2}:\d{2}$/', $datetime)) {
        return time();
    }
    list($date, $time) = explode(' ', $datetime);
    list($y, $m, $d) = explode('-', $date);
    list($h, $i, $s) = explode(':', $time);
    return gmmktime($h, $i, $s, $m, $d, $y); // 回傳 UTC 時間戳
}

結語

掌握 UNIX_TIMESTAMP / FROM_UNIXTIME 與 PHP time/date/strtotime 的對應關係，並統一使用 UTC 存儲、前端按時區顯示，即可避開 90% 的時間處理坑。