ISO 8601 MySQL数据库时间戳记:MySQL日期时间值不正确 [英] ISO 8601 Timestamp for MySQL Database: MySQL Incorrect datetime value
问题描述
错误日志:
{[错误:日期时间值不正确:'2012-08-24T17:29:11.683Z' b $ b列'机器人_refreshed_at'在行1]号码:1292,sqlStateMarker:
'#',sqlState:'22007',消息:'不正确的datetime值:
\'2012-08- 24T17:29:11.683Z \'在行\'robot_refreshed_at\'在行
1',sql:'INSERT INTO用户
(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:
[功能],emit:[功能] addListener:[Function],on:[Function],
once:[Function],removeListener:[Function],removeAllListeners:
[Function],listeners:[Function]}
我使用这个pi我的 Node.js中的代码的代码
如果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\')
/ pre>
我做错了吗?我刚从服务器中的表中使用
PHPMyAdmin
复制表。
非常感谢。 b $ b
解决方案如日期和时间文字:
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'
。
作为
YYYYMMDDHHMSMSS中的一个数字
或YYMMDDHHMMSS
格式,前提是数字作为日期有意义。例如,19830905132800
和830905132800
被解释为'1983-09-05 13:28 :00'
。
A
DATETIME
或TIMESTAMP
值可以包含最多微秒(6位数)精度的尾随小数秒。虽然这个小数部分被识别,但是从存储在DATETIME
或TIMESTAMP
列。有关MySQL中分数秒支持的信息,请参阅第11.3.6节时间分数秒 a>。
您的日期文字
'2012-08-24T17:29:11.683Z' code>不适合任何这些格式;建议您使用
-
使用Node.js Date对象的
toLocaleFormat()
方法(请确保MySQL连接的时区与Node .js的locale):if s instanceof Date
return s.toLocaleFormat(%Y-%m-% d%H:%M:%S)
-
使用Node.js Date对象
valueOf()
方法从UNIX时代起,以1000
(从UNIX时代开始获得秒)获得毫秒中的时间值,并通过MySQL的FROM_UNIXTIME( )
功能。
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] }
I use this piece of code in my Node.js
if s instanceof Date
return s.toISOString()
and updated them in database.
The SQL
insert expression follows:
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.
Thanks a lot.
As stated in Date and Time Literals:
MySQL recognizes
DATETIME
andTIMESTAMP
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
orYYMMDDHHMMSS
format, provided that the number makes sense as a date. For example,19830905132800
and830905132800
are interpreted as'1983-09-05 13:28:00'
.A
DATETIME
orTIMESTAMP
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 intoDATETIME
orTIMESTAMP
columns. For information about fractional seconds support in MySQL, see Section 11.3.6, "Fractional Seconds in Time Values".
Your date literal of '2012-08-24T17:29:11.683Z'
does not fit any of these formats; suggest you either—
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):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 by1000
(to get seconds since the UNIX epoch) and pass through MySQL'sFROM_UNIXTIME()
function.
这篇关于ISO 8601 MySQL数据库时间戳记:MySQL日期时间值不正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!