Geek asks and answers

ISO 8601 Timestamp for MySQL Database: MySQL Invalid Date and Time

Error Log:

{[Error: incorrect date and time value: '2012-08-24T17: 29: 11.683Z' for column 'robot _refreshed_at' on line 1] number: 1292, sqlStateMarker: '#', sqlState: '22007', message: 'Invalid date and time: \' 2012-08-24T17: 29: 11.683Z \ 'for column \ robot_refreshed_at \ in row 1', sql: 'INSERT INTO users (number, 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 the database.

Insert insert statement 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\')

Am I doing something wrong? I just copied the table using PHPMyAdminfrom the table on the server.

Many thanks.

+5
source share
2 answers

As indicated in the Literature Date and Time :

MySQL recognizes DATETIMEand TIMESTAMPvalues in the following formats:

  • Like a string in 'YYYY-MM-DD HH:MM:SS'or format 'YY-MM-DD HH:MM:SS'. The "relaxed" syntax is also allowed here: any punctuation character can be used as a separator 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.

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

A DATETIME TIMESTAMP - (6 ). , , DATETIME TIMESTAMP, MySQL . 11.3.6, " " .

'2012-08-24T17:29:11.683Z' ; :

+9

:

MySQL DATETIME: ISO:: 8601?

, 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

+2

More articles:


All Articles