mysql - MySQL 数据库 : MySQL Incorrect datetime value 的 ISO 8601 时间戳

Question

错误日志:

{ [Error: Incorrect datetime value: '2012-08-24T17:29:11.683Z' for column 'robot _refreshed_at' at row 1] number: 1292, sqlStateMarker: '#', sqlState: '22007', message: 'Incorrect datetime value: \'2012-08-24T17:29:11.683Z\' for column \' robot_refreshed_at\' at row 1', 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\')', 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 从服务器中的表中复制了一个表。

非常感谢。

Answer 1

如 Date and Time Literals 中所述:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

'2012-08-24T17:29:11.683Z' 的日期文字不符合这些格式；建议你——

• 改用 Node.js 日期对象的 toLocaleFormat()方法(确保 MySQL 连接的时区与 Node.js 的语言环境的时区相匹配):

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

• 使用 Node.js 日期对象的 valueOf()方法获取自 UNIX 纪元以来以 毫秒 为单位的时间值，除以 1000(得到自 UNIX 纪元以来的 秒)并通过 MySQL 的FROM_UNIXTIME()功能。