如何创建MySQL日期时间列使其在浏览器正确显示格式与时区

• 接口返回的2022-06-12T00:36:20.000Z是JavaScript Date对象JSON序列化时默认输出的UTC时区ISO 8601格式，末尾Z代表0时区
• 3小时时差来自时区配置不匹配：MySQL的TIMESTAMP类型存储时会自动把时间转为UTC，查询时按数据库连接的时区转回对应时间；Node.js连接MySQL默认使用0时区读取数据，和你本地UTC+3时区存在固定偏移
• 现有代码还存在字段名不匹配问题：数据库时间字段名为date，前端渲染时取的是dato.fecha，不处理会直接显示undefined

推荐优先用后端统一处理的方案，兼容性最好，不需要依赖用户浏览器的时区配置。

方案1：后端统一配置时区+格式化输出（推荐） #

1. 修改Knex连接配置（db/db.js文件），添加和你所在地区匹配的时区参数，比如UTC+3就填+03:00，国内UTC+8就填+08:00：

// 原有knex配置的connection节点下新增timezone字段
const knex = require('knex')({
  client: 'mysql2', // 匹配你实际使用的mysql驱动
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: '你的数据库用户名',
    password: '你的数据库密码',
    database: '你的数据库名',
    timezone: '+03:00' // 替换为你本地的UTC时区偏移
  }
});

2. 修改models/chat.js的getMessages方法，查询时直接把时间格式化为YYYY-MM-DD HH:mm:ss格式，同时起别名为fecha匹配前端字段：

async getMessages() {
  try {
    const chat = await this.knex(this.table).select(
      'id',
      'email', 
      'msg',
      this.knex.raw('DATE_FORMAT(`date`, "%Y-%m-%d %H:%i:%s") as fecha')
    );
    return chat;
  } catch (error) {
    console.log(error);
  }
}

改完后接口会直接返回时区正确、格式符合预期的时间字符串，前端不需要任何修改就能正常展示。

方案2：前端格式化时间（不改动后端） #

如果不想调整后端逻辑，直接在前端拿到数据后，把时间转为本地时区再格式化。修改socket.js中chat事件的渲染逻辑：

socket.on("chat", () => {
  fetch("api/chat")
    .then((response) => response.json())
    .then((data) => {
      chat.innerHTML = "";
      data.forEach((dato) => {
        // 转换为本地时区并格式化
        const timeObj = new Date(dato.date);
        const localTime = `${timeObj.getFullYear()}-${String(timeObj.getMonth()+1).padStart(2,'0')}-${String(timeObj.getDate()).padStart(2,'0')} ${String(timeObj.getHours()).padStart(2,'0')}:${String(timeObj.getMinutes()).padStart(2,'0')}:${String(timeObj.getSeconds()).padStart(2,'0')}`;
        
        chat.innerHTML += `<div class="card">
        <div class="card-header bg-light">
          <h5 class="card-title text-primary">${dato.email}</h5>
          <h6 class="card-subtitle bg-light text-success mb-2">${localTime}</h6>
        </div>
        <div class="card-body bg-dark">
          <p class="card-text text-warning bg-dark">${dato.msg}</p>
        </div>
      </div>
      <br>`;
      });
    });
});

注意：该方案会以用户浏览器所在时区为准显示时间，如果用户跨时区访问会显示对应地区的时间，不适合要求时间统一的场景。

可选表结构调整 #

如果不需要TIMESTAMP的自动时区转换特性，可以把字段改为DATETIME类型，存储和读取时不会做时区转换，存什么值取出来就是什么值：

ALTER TABLE mensajes MODIFY COLUMN `date` DATETIME DEFAULT CURRENT_TIMESTAMP;

该修改仅对新插入的数据生效，历史数据的时差问题需要手动修正。

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