MySQL数据库的ISO 8601时间戳：MySQL不正确的日期时间值

Question

错误日志：

{[Error: 日期时间值不正确：'2012-08-24T17:29:11.683Z'，列名为 'robot_refreshed_at'，行号为1] number: 1292, sqlStateMarker: '#', sqlState: '22007', message: '日期时间值不正确：\'2012-08-24T17:29:11.683Z\'，列名为 \' robot_refreshed_at\'，行号为1', sql: 'INSERT INTO users (id,name,count_moments,count_likes,count_followers,robot_refreshed_at,robot_count_followers) VALUES (\'1834084\',\'NNNyingzi\',\'5\',\ '0\',\'0\',\'2012-08-24T17:29:11.683Z\',\'0\')', setMaxListeners: [Function], emit: [Function], addListener: [Function], on: [Function], once: [Function], removeListener: [Function], removeAllListeners: [Function], listeners: [Function]}

我在我的Node.js中使用了这段代码。

  if s instanceof Date
         return s.toISOString()

并将它们更新到数据库中。

SQL 插入表达式如下：

     INSERT INTO users (id,name,count_moments,count_likes,count_followers,rob ot_refreshed_at,robot_count_followers) VALUES (\'1834084\',\'NNNyingzi\',\'5\',\ '0\',\'0\',\'2012-08-24T17:29:11.683Z\',\'0\')

我有问题吗？我只是使用PHPMyAdmin从服务器中复制了一张表格。

非常感谢。

- Chao Zhang

Answer 1

根据日期和时间字面量的说明：

MySQL可识别以下格式的DATETIME和TIMESTAMP值：

• 字符串，格式为'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'。这里也允许使用“松散”语法：任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如，'2012-12-31 11:30:45'、'2012^12^31 11+30+45'、'2012/12/31 11*30*45'和'2012@12@31 11^30^45'都是等价的。

• 没有分隔符的字符串，格式为'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'，只要该字符串可以理解为日期即可。例如，'20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28'，但'071122129015'是不合法的（它有一个无意义的分钟部分）并变成'0000-00-00 00:00:00'。

• 数字，格式为YYYYMMDDHHMMSS或YYMMDDHHMMSS，只要该数字可以理解为日期即可。例如，19830905132800和830905132800被解释为'1983-09-05 13:28:00'。

DATETIME或TIMESTAMP值最多可以包含尾随的小数秒部分，精度高达微秒（6位）。虽然这个小数部分会被识别，但它会从存储到DATETIME或TIMESTAMP列的值中丢弃。有关MySQL中小数秒支持的信息，请参见第11.3.6节“时间值中的小数秒”。

您的日期字面量'2012-08-24T17:29:11.683Z'不符合这些格式；建议您要么—

• 改用Node.js Date对象的toLocaleFormat()方法（确保MySQL连接的时区与Node.js的语言环境匹配）：

    if s instanceof Date
           return s.toLocaleFormat("%Y-%m-%d %H:%M:%S")
  

• 使用 Node.js 的 Date 对象的 valueOf() 方法获取自 UNIX 纪元以来的时间值（以毫秒为单位），将其除以 1000（以获取自 UNIX 纪元以来的秒数），然后通过 MySQL 的 FROM_UNIXTIME() 函数。

- eggyal

---

它在不同版本的MySQL中有所改变吗？我确信从中复制表的服务器正在正常运行。 - Chao Zhang

@ComboZhc：据我所知，不是这样的。也许是PHPMyAdmin将输出格式化为这种方式以便显示，但该格式并不是有效的MySQL日期时间文字。如果需要进行转换，您可以使用STR_TO_DATE()函数。 - eggyal

如果您在设置毫秒和/或微秒日期时间时遇到问题，可以禁用MySQL的严格模式（设置sql_mode=''）。 - momo

@DanieleCruciani：请注意，这将始终生成UTC字面值，而MySQL可能会将其视为会话时区。最好首先将会话时区设置为UTC，以避免任何翻译错误。 - eggyal

值得注意的是，toLocaleFormat方法已被弃用。 - Asd20752

Answer 2

我在这个链接上找到了它： MySQL插入DATETIME：使用ISO8601格式是否安全？ 似乎插入ISO8601时间戳并不安全。它取决于MySQL的解析器。可能不同版本使用不同的方法。

Date.prototype.format = (format) ->
  o = { 
    "(M+)" : this.getMonth()+1,
    "(d+)" : this.getDate(),
    "(h+)" : this.getHours(),
    "(m+)" : this.getMinutes(),
    "(s+)" : this.getSeconds(),
    "(q+)" : Math.floor((this.getMonth()+3)/3),
    "(S)" : this.getMilliseconds()
  } 
  if /(y+)/.test(format)
    format = format.replace(RegExp.$1, (this.getFullYear()+"").substr(4 - RegExp.$1.length))
  for k, v of o
    if (new RegExp(k)).test(format)
       format = format.replace(RegExp.$1, if RegExp.$1.length == 1 then o[k] else ('00'+ o[k]).substr((''+ o[k]).length))
  return format

这段代码可以为node.js提供格式化Date的功能。

- Chao Zhang