# mysql2 讀 DATETIME 差 8 小時，一行 timezone: 'Z' 解決

- URL: https://justfly.idv.tw/mysql2-%e8%ae%80-datetime-%e5%b7%ae-8-%e5%b0%8f%e6%99%82%ef%bc%8c%e4%b8%80%e8%a1%8c-timezone-z-%e8%a7%a3%e6%b1%ba/
- 日期: 2026-10-19
- 分類: 我知故我在
- 標籤: javascript, mysql2, node.js, 時區

![mysql2 讀 DATETIME 差 8 小時，一行 timezone: ]
環境：Node.js 搭配 mysql2（`createPool`），MySQL 欄位型別為 `DATETIME`，資料約定以 [UTC](https://zh.wikipedia.org/wiki/協調世界時) 寫入，Node 主機的行程時區是 UTC+8。症狀：讀出來的 `Date` 整整早 8 小時，不報錯，只是算錯。

受害的通常是兌換碼、優惠券、活動的開始與到期時間。預定台灣時間 11/29 00:00 到期的碼，在 11/28 16:00 就被判定過期；開始時間也同樣提早生效。開發機與正式機時區不同的團隊特別容易漏掉。

##### 根因：DATETIME 沒有時區，驅動自己補了一個

MySQL 的 `DATETIME` 只存「年月日時分秒」，不帶任何時區資訊。mysql2 的 `timezone` 選項預設為 `'local'`，讀取時會把欄位值當成 Node 行程所在時區（這裡是 +08:00）的時間來建立 JS `Date`。

資料原本是以 UTC 寫入的 `16:00:00`，被當成 +08 的 16:00，換算成絕對時刻就是 UTC 的 08:00，早了 8 小時。寫入方向同樣有問題：傳入 `Date` 物件時，驅動也依本地時區轉換成字串。誤差固定等於行程時區偏移，UTC+8 的主機上 `getTimezoneOffset()` 回傳 `-480`，剛好 8 小時。

另一個讓問題藏得很深的情況：如果某個管理介面自己改用 UTC 解讀，畫面顯示會是正常的，但其他走預設連線的程式碼仍然是錯的，所以不容易察覺。

##### 修法：在 pool 統一設定

- 先確認資料庫裡的 `DATETIME` 真的是以 UTC 寫入。

- 在建立連線池的地方統一加上 `timezone: 'Z'`，讓 mysql2 讀寫 `DATETIME` 都用 UTC。不要在個別查詢或個別頁面各自補 8 小時。

- 所有查詢都走同一個 pool，避免有一條舊路徑還在用預設的 `'local'`。

- 部署前用已知時間的資料讀回比對（下面的 `check-tz.js`）。

- 測試、預發、正式每個環境都要確認，各台主機的行程時區可能不同。

```
// db.js — 所有查詢共用這個 pool
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,      // e.g. db.example.com
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  // 關鍵：DATETIME 一律以 UTC 讀寫，不受 Node 行程時區(TZ)影響
  timezone: 'Z',
});

module.exports = pool;

// check-tz.js — 驗證：寫入一個已知的 UTC 時間再讀回
// node check-tz.js
const pool = require('./db');
(async () => {
  const [rows] = await pool.query(
    "SELECT CAST('2026-11-28 16:00:00' AS DATETIME) AS t"
  );
  console.log('process TZ offset (min):', new Date().getTimezoneOffset());
  console.log('read back as ISO      :', rows[0].t.toISOString());
  // 期望輸出 2026-11-28T16:00:00.000Z
  // 若看到 2026-11-28T08:00:00.000Z，代表驅動仍用 +08 本地時區解讀
  await pool.end();
})();
```

整個修正只動連線設定，資料表變更為 0 張。三個環境都部署後，問題消失。

##### 驗證環境

在實際部署的主機上執行 `check-tz.js`，筆電上跑沒有意義，因為筆電的時區未必和正式機相同。讀回的 `toISOString()` 與寫進去的 UTC 字串相同就正常；差了本機時區偏移（例如 UTC+8 的主機上變成 `2026-11-28T08:00:00.000Z`）就是中招。

另一個做法是分別執行：

```
TZ=Asia/Taipei node check-tz.js
TZ=UTC node check-tz.js
```

修好之後兩者輸出應該一致；沒修則會不同。這個比對不需要知道主機實際時區，適合放進部署檢查。

##### 適用範圍與限制

- 適用於 Node.js 搭配 mysql2、欄位型別為 `DATETIME` 且約定存 UTC 的系統。

- 主機時區本身就是 UTC 時不會發作，所以只在開發機與正式機時區不同時才現形。

- `TIMESTAMP` 欄位還會受 MySQL session 的 `time_zone` 影響，需另外確認，不在這個修法的涵蓋範圍。

- 如果原本有程式碼手動補償 8 小時，改設定後要一起移除，否則會變成反方向錯 8 小時。

排查順序建議：先看有沒有手動補償的程式碼，再確認 pool 是不是唯一入口，最後才在各環境跑驗證腳本。

— 邱柏宇

### mysql2 Reads DATETIME 8 Hours Off: Set timezone to Z

Environment: Node.js with mysql2 (`createPool`), a MySQL `DATETIME` column that by convention stores [UTC](https://en.wikipedia.org/wiki/Coordinated_Universal_Time), and a Node host whose process time zone is UTC+8. Symptom: the `Date` objects read back are exactly 8 hours early. No error is thrown; the times are just wrong.

Redeem codes, coupons, and event start/end times are the usual casualties. A code meant to expire at 00:00 on 11/29 Taiwan time gets judged expired at 16:00 on 11/28, and start times take effect early in the same way. Setups where the dev machine and the production host run in different time zones are especially prone to missing it.

##### Root cause: DATETIME has no time zone, so the driver supplies one

MySQL’s `DATETIME` stores only year-through-second and carries no time zone information. mysql2’s `timezone` option defaults to `'local'`, so on read the driver treats the column value as a time in the Node process’s zone (+08:00 here) when building the JS `Date`.

A value written as `16:00:00` UTC is interpreted as 16:00 at +08, which converts to 08:00 UTC as an absolute instant: 8 hours early. Writes have the same problem, since passing a `Date` object makes the driver convert it using the local zone. The error always equals the process offset; on a UTC+8 host, `getTimezoneOffset()` returns `-480`, exactly 8 hours.

One thing makes this hard to spot: if some admin page decodes values as UTC on its own, its display looks correct while every other code path on the default connection stays wrong.

##### The fix: set it once on the pool

- Confirm that the `DATETIME` values in the database really were written as UTC.

- Add `timezone: 'Z'` where the pool is created so mysql2 reads and writes `DATETIME` in UTC. Do not add 8 hours per query or per page.

- Route every query through that one pool so no legacy path still uses the default `'local'`.

- Before deploying, read back a known value (the `check-tz.js` script below).

- Check every environment (test, staging, production), because process time zones can differ per host.

```
// db.js — 所有查詢共用這個 pool
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,      // e.g. db.example.com
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  // 關鍵：DATETIME 一律以 UTC 讀寫，不受 Node 行程時區(TZ)影響
  timezone: 'Z',
});

module.exports = pool;

// check-tz.js — 驗證：寫入一個已知的 UTC 時間再讀回
// node check-tz.js
const pool = require('./db');
(async () => {
  const [rows] = await pool.query(
    "SELECT CAST('2026-11-28 16:00:00' AS DATETIME) AS t"
  );
  console.log('process TZ offset (min):', new Date().getTimezoneOffset());
  console.log('read back as ISO      :', rows[0].t.toISOString());
  // 期望輸出 2026-11-28T16:00:00.000Z
  // 若看到 2026-11-28T08:00:00.000Z，代表驅動仍用 +08 本地時區解讀
  await pool.end();
})();
```

The whole change lives in connection config: 0 table changes. After deploying to all three environments, the problem disappeared.

##### Verifying the environment

Run `check-tz.js` on the actual deployment host; running it on a laptop proves little because the laptop’s zone may differ from production. If the `toISOString()` output matches the UTC string that was put in, all is well. If it is off by the host’s offset (for example `2026-11-28T08:00:00.000Z` on a UTC+8 host), the bug is present.

Alternatively, run the script twice:

```
TZ=Asia/Taipei node check-tz.js
TZ=UTC node check-tz.js
```

When fixed, both runs print the same output; when not, they differ. This comparison does not require knowing the host’s real zone, which makes it easy to drop into a deploy check.

##### Scope and limits

- Applies to Node.js with mysql2, where the column type is `DATETIME` and the convention is to store UTC.

- Hosts that already run in UTC never trigger it, so it only shows up when the dev and production time zones differ.

- `TIMESTAMP` columns are additionally affected by the MySQL session `time_zone` and need a separate check; this fix does not cover them.

- If any code manually compensates by 8 hours, remove it along with the config change, otherwise the error flips to 8 hours in the opposite direction.

Suggested order of investigation: look for manual offset compensation first, confirm the pool is the single entry point, then run the verification script in each environment.

— 邱柏宇