mysql - 检索和插入日期时间时 MYSQL 的默认行为是什么？遇到一个有趣的时区偏移问题

Question

我们的测试环境中有一个采用 UTC 时区的服务器，并注意到 PDT 中的时间显示不正确。

我们提出的解决方案是在将所有内容存储到数据库之前将其转换为 UTC 时间，然后在检索时使用 moment-timezone 将其转换回正确的区域设置时区。

我遇到了一个有趣的问题，其中 UTC 时区转换工作正常(例如，如果事件发生在太平洋夏令时间上午 8:00，它将其存储为 15:00 UTC，正确的时差为 +7 小时)。所有转换都在客户端进行，然后作为字符串传递到服务器，直到最终存储到数据库中。在数据库中运行查询后，我看到它的格式正确，为 15:00。

但是，当我检索数据时，它又添加了 7 小时并返回 22:00。我已经在所有 Controller 路由上记录了这一点，并确认服务器上任何地方的日期对象都没有发生其他额外的修改，所以我是否正确地假设这是我需要注意的 MYSQL 的默认行为的？

有人知道为什么会发生这种情况吗？

使用 moment-timezone 将本地客户端时间转换为 UTC 并将其存储为字符串。将其传递给 Controller ​​以插入数据库。

已确认数据库正在按预期正确存储信息。

加上 7 个小时以上，输出不正确。

// This is on the client side where it formats the date using moment to convert it to UTC from locale time
function convertTimeToUTCTimezone(date) {
  const timezone = "America/Vancouver"; // TODO make this more applicable across different time zones in the future

  const formatDate = moment(date).format('YYYY-MM-DD HH:mm:ss');
  const formatTimezoneDate = moment.tz(formatDate, 
  timezone).utc().format('YYYY-MM-DD HH:mm:ss');
  return formatTimezoneDate;
}

表格中输入的日期是:2019-06-14 8:00:00(太平洋夏令时间上午 8 点，UTC - 7 小时)

从客户端传递到服务器的日期字符串是:2019-06-14T15:00:00.000Z

我希望通过 MySQL 查询看到的日期时间是:(正确)2019-06-12 15:00:00

我希望从中检索到的时间等于:(预期)2019-06-14T15:00:00.000Z

但我得到:(问题)2019-06-14T22:00:00.000Z

[更新]mysql数据库，列定义为DATETIME

插入示例:

// Using objectionJS
static async function insertToTable(formData) {
  // formData is an object of key-value pairs, keys matching database column names
  return this.query().insert(formData);
}

Answer 1

我很好奇实际 MySQL“INSERT”语句中“日期/时间”列的值是什么样的。我也很好奇你在 MySql 表中将其定义为什么类型。

总的来说，我建议:

• 将日期、时间和日期时间列定义为“DATETIME”类型

• 如果可能，请设置默认约束并为 INSERT 使用“NOW()”。这将使用服务器的日期/时间/时区。

从此链接:

https://www.vertabelo.com/blog/technical-articles/the-proper-way-to-handle-multiple-time-zones-in-mysql

A complete generic approach would be to have two DATETIME columns to store the utc_datetime and the local_datetime, while a VARCHAR column stores the IANA time_zone string (the longest one at the moment is “America/Argentina/ComodRivadavia”, which has 32 characters).

无论您决定如何，我都会考虑在 MySql/服务器端完成大部分“工作”，而不是在 Javascript 客户端中完成。