環境：Node.js 搭配 mysql2（createPool），MySQL 欄位型別為 DATETIME，資料約定以 UTC 寫入，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 統一設定

1. 先確認資料庫裡的 DATETIME 真的是以 UTC 寫入。
2. 在建立連線池的地方統一加上 timezone: 'Z'，讓 mysql2 讀寫 DATETIME 都用 UTC。不要在個別查詢或個別頁面各自補 8 小時。
3. 所有查詢都走同一個 pool，避免有一條舊路徑還在用預設的 'local'。
4. 部署前用已知時間的資料讀回比對（下面的 check-tz.js）。
5. 測試、預發、正式每個環境都要確認，各台主機的行程時區可能不同。

// 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, 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

1. Confirm that the DATETIME values in the database really were written as UTC.
2. 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.
3. Route every query through that one pool so no legacy path still uses the default 'local'.
4. Before deploying, read back a known value (the check-tz.js script below).
5. 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.

— 邱柏宇

延伸閱讀

• [node.js]在ec2上安裝node.js實作即時聊天室
• 400 不等於壞掉，只是還沒好
• 週報第一行偶發亂碼：UTF-8 字元被 TCP chunk 切半
• 副檔名是最不可靠的承諾
• [D2C]ブレイブナイツ