ISO 8601 Timestamp for MySQL Database: MySQL Incorrect datetime value

Question

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.

eggyal · Accepted Answer · 2012-08-28T01:22:48.347

15

As stated in 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”.

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 by 1000 (to get seconds since the UNIX epoch) and pass through MySQL's FROM_UNIXTIME() function.

edited Aug 28 '12 at 01:22

answered Aug 24 '12 at 17:42

eggyal

122,705
18
212
237

2

Has it changed over different versions of `MySQL`? I am sure that on the server where I copied the table from is running properly. – Chao Zhang Aug 24 '12 at 17:48
@ComboZhc: Not to my knowledge. Perhaps PHPMyAdmin formats the output for display in that way, but the format is not a valid MySQL datetime literal. You can use [`STR_TO_DATE()`](http://dev.mysql.com/doc//en/date-and-time-functions.html#function_str-to-date) to perform conversions, if required. – eggyal Aug 24 '12 at 17:49
If you have problems setting datetimes with millis and/or microseconds you can disable mysql strict mode (set sql_mode='') – momo May 09 '14 at 04:19
toLocaleString() not supported by node, I wrote `s.toISOString().substr(0,19).replace('T',' ');` that may look horrible, but it is ok, always – Daniele Cruciani Feb 12 '20 at 15:01
1

@DanieleCruciani: just beware that that will always produce a literal in UTC whereas MySQL may understand it to be in the session’s timezone. Best first set the session timezone to UTC to avoid any translation errors. – eggyal Feb 12 '20 at 15:04
@eggyal I see, you mean something like `SET @@session.time_zone = "+00:00";` before. On the other hand I am using https://locutus.io/ because of speedup some migration, so date("Y-m-d H:i:s") will do it clearly. I tried also `s.toLocaleTimeString('it-IT')` for time is ok, but no date. At the end `FROM_UNIXTIME()` and `Math.round(s.getTime()/1000)` is the better solution for me, at least it is clear and readable, and does not require so much resources – Daniele Cruciani Feb 13 '20 at 17:25
It is also worth noting that the `toLocaleFormat` method has been deprecated. – Asd20752 Jul 18 '21 at 11:39

score 2 · Answer 2 · edited May 23 '17 at 12:33

I find it on this link:

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

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

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

ISO 8601 Timestamp for MySQL Database: MySQL Incorrect datetime value

2 Answers2

Linked