Timezone conversion issue

[wmertens]

I adapted @chaoran's code from #538 to show an issue with manual escaping:

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)', 
  function(err) {
    if (err) throw err;

    // Make sure milliseconds aren't an issue
    var date = new Date(Math.floor(Date.now()/1000)*1000);

    conn.query('INSERT INTO datetime_bug SET created_at = '+mysql.escape(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 will throw things like AssertionError: "2013-07-12T07:39:55.000Z" == "2013-07-12T05:39:55.000Z" unless you're living in UTC.

To fix this, you need to call escape(date, true, 'local'). The issue is that the escaping converts to UTC but removes timezone information and mysql thinks that the date is a local date.

Maybe the escape function should always convert dates to a CONVERT_TZ() call so there is never an issue no matter what the local and remote time zones.

[dresende]

There should be a db.escape wrapping the internal one with the connection timezone set.

[dresende]

Try calling conn.escape(date). Or:

conn.query('INSERT INTO datetime_bug SET created_at = ?', [ date ], function(err, result) {
    // ...
});

[wmertens]

@dresende that's exactly what I do, I call mysql.escape and it doesn't handle the time zone correctly unless you add the parameters true, 'local'. The automatic one with "?" does do it.

[dougwilson]

Essentially if you are going to use escape, then there are two methods:

mysql.escape which is independent of any configuration, which essentially means you have to pass all the parameters, as none are really optional.

connection.escape which will use the configuration from the connection for any of the missing parameters.

The only possible solution that I think would do something to help would be to change the SqlString.escape function to default the time zone to local instead of UTC to match the default of the connection configuration of the time zone.

This is what should be within the SqlString.escape function instead of what is currently there:

  if (val instanceof Date) {
    val = SqlString.dateToString(val, timeZone || "local");
  }

[dresende]

Agree