MySQL数据库的ISO 8601时间戳:MySQL不正确的datetime值。

Error log:

错误日志:

{ [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] }

{[错误:不正确的日期时间值:'2012-08-24T17:29:11.683Z'的列'robot _refreshed_at'(第1行):1292,sqlStateMarker: '#'， sqlState: '22007'，消息:'不正确的datetime值:\' 2012-08-24t17 . 11.683z \'在第一行' '，sql: ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' ' ' . ' ' . ' ' ' ' . ' '【功能】、emit:【功能】、addListener:【功能】、on:【功能】、once:【功能】、removeListener:【功能】、removealllistener:【功能】、监听器:【功能】

I use this piece of code in my Node.js

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

  if s instanceof Date
         return s.toISOString()

and updated them in database.

并在数据库中更新。

The SQL insert expression follows:

SQL insert表达式如下:

     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\')

Am I doing anything wrong? I just copied a table using PHPMyAdmin from a table in server.

我做错什么了吗?我刚刚使用phmypadmin从服务器中的一个表复制了一个表。

Thanks a lot.

非常感谢。

2 个解决方案

#1

As stated in Date and Time Literals:

如日期和时间文字所述:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

MySQL识别这些格式的DATETIME和TIMESTAMP值:

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.

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

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'.

作为一个字符串，在“yyyyyymmddhhmmss”或“YYMMDDHHMMSS”格式中没有分隔符，只要字符串作为日期是有意义的。例如，“20070523091528”和“070523091528”被解释为“2007-05-23 15:28”，但是“071122122129015”是非法的(它有一个无意义的分句)，变成了“0000-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'.

作为yyyyymmddhhmmss或YYMMDDHHMMSS格式的数字，但该数字作为日期是有意义的。例如，19830905132800和830905132800被解释为“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”.

一个DATETIME或TIMESTAMP值可以包含一个以微秒(6位数)为精度的小数秒部分。虽然可以识别这个小数部分，但是它会从存储到DATETIME或TIMESTAMP列的值中被丢弃。有关MySQL中小数秒支持的信息，请参见11.3.6节“时间值中的小数秒”。

Your date literal of '2012-08-24T17:29:11.683Z' does not fit any of these formats; suggest you either—

您的日期文字‘2012-08-24 t17:29 11.683z’不适合任何一种格式;建议你,

use instead the Node.js Date object's toLocaleFormat() method (be sure that the timezone of the MySQL connection matches that of Node.js's locale):

用相反的节点。js Date对象的toLocaleFormat()方法(请确保MySQL连接的时区与节点的时区匹配。js的场所):
```
  if s instanceof Date
         return s.toLocaleFormat("%Y-%m-%d %H:%M:%S")
```
use the Node.js Date object's valueOf() method to obtain the time's value in milliseconds since the UNIX epoch, divide by 1000 (to get seconds since the UNIX epoch) and pass through MySQL's FROM_UNIXTIME() function.

使用节点。js Date对象的valueOf()方法获取UNIX纪元以来的时间值(以毫秒为单位)，除以1000(从UNIX纪元得到秒)，并通过MySQL的FROM_UNIXTIME()函数。

#2

I find it on this link:

我在这个链接上找到它:

MySQL insert to DATETIME: is it safe to use ISO::8601 format?

MySQL插入到DATETIME:使用ISO::8601格式是否安全?

It seems that inserting an ISO8601 timestamp is not safe. It depends on the parser of MySQL. Maybe different versions use different methods.

似乎插入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

This piece code could provide node.js with the ability to format a Date

这段代码可以提供节点。具有格式化日期的能力

#1