MySQL datetime类型在NodeJS API查询时小时显示错误求助

MySQL中用datetime类型存储时间，直接执行SELECT查询时显示正确（数据库中存储的是18:30，查询结果也为该值），但通过NodeJS API执行相同查询时，返回的时间小时错误，示例返回值为"2022-10-14T16:30:00.000Z"（比实际时间晚2小时）。

相关配置：

• MySQL时区查询结果：

  SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
  +--------------------+---------------------+
  | @@GLOBAL.time_zone | @@SESSION.time_zone |
  +--------------------+---------------------+
  | SYSTEM             | SYSTEM              |
  +--------------------+---------------------+
  

• game表创建语句：

  CREATE TABLE `game` (
    `id` int NOT NULL AUTO_INCREMENT,
    `fk_reservation` int DEFAULT NULL,
    `fk_team` int DEFAULT NULL,
    `fk_location` int DEFAULT NULL,
    `datetime` datetime DEFAULT NULL,
    `opponent` varchar(42) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `game_location_id_fk` (`fk_location`),
    KEY `game_team_id_fk` (`fk_team`),
    KEY `game_reservation_id_fk` (`fk_reservation`),
    CONSTRAINT `game_location_id_fk` FOREIGN KEY (`fk_location`) REFERENCES `location` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `game_reservation_id_fk` FOREIGN KEY (`fk_reservation`) REFERENCES `reservation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `game_team_id_fk` FOREIGN KEY (`fk_team`) REFERENCES `team` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

datetime类型本身不存储时区信息，MySQL直接查询时会用服务器本地时区（SYSTEM对应的时区）显示时间；但NodeJS的MySQL驱动（如mysql或mysql2）默认会把数据库返回的datetime值当作UTC时间处理，转成带Z后缀的JavaScript Date对象，导致时间和数据库本地时区显示的结果出现偏差。

1. 连接MySQL时指定时区 #

在NodeJS的数据库连接配置里，明确设置timezone参数为服务器本地时区或具体时区值，让驱动自动转换为正确时间：

// 以mysql2模块为例
const mysql = require('mysql2/promise');

async function connect() {
  const connection = await mysql.createConnection({
    host: '你的数据库地址',
    user: '用户名',
    password: '密码',
    database: '数据库名',
    timezone: 'local' // 使用服务器本地时区，也可以指定具体时区如'+02:00'
  });
  // 后续查询操作...
}

2. 查询时直接转换时间格式 #

在SELECT语句中，用MySQL函数把datetime转成本地时区的字符串格式，避免驱动自动转换：

SELECT 
  id,
  DATE_FORMAT(`datetime`, '%Y-%m-%d %H:%i:%s') AS datetime,
  opponent
FROM game;

也可以用CONVERT_TZ函数明确转换时区（需确保MySQL时区表已加载）：

SELECT 
  id,
  CONVERT_TZ(`datetime`, @@session.time_zone, '+02:00') AS datetime,
  opponent
FROM game;

3. 验证MySQL服务器实际时区 #

虽然查询显示时区为SYSTEM，可执行以下语句确认服务器实际对应的时区时差：

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

如果返回02:00:00，说明服务器时区是UTC+2，和你遇到的时间偏差一致，确认时区配置无问题。

内容的提问来源于stack exchange，提问作者Eliot Bond