DATETIME type conversion problem

[chaoran]

The date object inserted into database, and what is retrieved from the database is different.

var mysql = require('mysql')
  , assert = require('assert');

var conn= mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});

conn.connect();

conn.query(
  'CREATE TABLE IF NOT EXISTS datetime_bug (id SERIAL, created_at DATETIME(3))', 
  function(err) {
    if (err) throw err;

    var date = new Date();

    conn.query('INSERT INTO datetime_bug SET ?', { 
      created_at: date
    }, function(err, result) {
      if (err) throw err;

      var id = result.insertId;

      conn.query('SELECT * FROM datetime_bug WHERE ?', { id: id }, function(err, row) {
        if (err) throw err;
        conn.end();
        assert.equal(date, row[0].created_at);
      });
    });
  }
);

This throws: AssertionError: "2013-07-11T16:28:26.218Z" == "2013-07-11T16:28:26.000Z"

[wmertens]

This is due to the conversion not adding the microseconds here https://github.com/felixge/node-mysql/blob/dd921c3dac1c5dc1afbc2149be6d10665973b9f2/lib/protocol/SqlString.js#L95

[dougwilson]

@chaoran what version of MySQL server are you connecting to?

@wmertens MySQL cannot store sub-second resolution in DATETIME until MySQL 5.6.4. Unless @chaoran is using MySQL 5.6.4 or higher, than the point is moot for him.

[chaoran]

@dougwilson Sorry. I should mention that I'm using mysql 5.6.12.

[dougwilson]

Thanks, @chaoran. So that means it would be nice to get this library to include milliseconds in the date stringification (by default? by a switch?).

[chaoran]

@dougwilson, I think it should be added by default. The milliseconds part is harmless because mysql will silently ignore that part if it does not support fraction of time.

[dougwilson]

Though MySQL doesn't ignore it, it produces a warning, and when strict is turned on, it will be fatal, which is why it wasn't included in the library before, I think.

[chaoran]

@dougwilson Really? It doesn't complain at all for me.

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| STRICT_ALL_TABLES  |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into datetime_bug SET created_at='2013-12-31 12:31:33.123';
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetime_bug ORDER BY id DESC LIMIT 1;
+----+---------------------+
| id | created_at          |
+----+---------------------+
| 10 | 2013-12-31 12:31:33 |
+----+---------------------+
1 row in set (0.00 sec)

The created_at column above is of the plain DATETIME type, not the new DATETIME(3) type.

[dresende]

If the milisecond support could be checked in server info packet on connection, it could be automatically added if supported. But I'm not sure the server does this.

[dougwilson]

Actually, I just ran what @chaoran did on 5.1.70 and it does indeed not issue any warnings or error on strict mode. @dresende that means you can just add in the milliseconds in the date string stringification and it should definitely be perfectly fine on all versions!