Date fields - timezone problem

[euronymous]

Hi,

I've spend the last hour for reverse engineering of that case...

Lets look at the file:
/loopback-connector-mysql/lib/mysql.js:353
val = new Date(val.toString().replace(/GMT.*$/, 'GMT'));
Why you don't give a sh about timezone?! This is at least weird.

Here is an example. I have a date from MySQL:
Mon Jun 01 2015 02:01:01 GMT+0200 (CEST)

So lets try:

        console.log(val);
        console.log(val.toString().replace(/GMT.*$/, 'GMT'));
        val = new Date(val.toString().replace(/GMT.*$/, 'GMT'));
        console.log(val);

And the result is:

        Mon Jun 01 2015 02:01:01 GMT+0200 (CEST)
        Mon Jun 01 2015 02:01:01 GMT
        Mon Jun 01 2015 04:01:01 GMT+0200 (CEST)

And at last the strongloop API response:
"2015-06-01T02:01:01.000Z"

Kind Regards,
Krzysiek

[bbito]

@euronymous I had an issue with non UTC dates in MySQL and found the dateString option useful for a workaround. It may help in your case as well - see issue 120 and daddybh's dateString option: #120

[DaGaMs]

I also just ran into this problem when writing some tests where I entered data into a mysql database directly, then fetched it with loopback. My understanding is that DATETIME fields are "timezone-free". Most libraries, including e.g. mysqljs interpret DATETIME objects in the local timezone by default. Loopback however turns them into UTC by default, which is a bit confusing.

[bbito]

@DaGaMs , Since this is a JavaScript/node system, I think this is the correct behavior for DATETIME and TIMESTAMP columns since JavaScript serializes its Date object into ISO 8601 'which is always 24 characters long: YYYY-MM-DDTHH:mm:ss.sssZ. The timezone is always zero UTC offset, as denoted by the suffix "Z".' See: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toJSON

Are you saying that if you retrieve the same DATETIME field with mysqljs (which is what this loopback connector uses) and serialize that javascript Date object with .toJSON() you get a different result than you see when you retrieve it as JSON through Loopback?

[superkhau]

Are you saying that if you retrieve the same DATETIME field with mysqljs (which is what this loopback connector uses) and serialize that javascript Date object with .toJSON() you get a different result than you see when you retrieve is as JSON through Loopback?

Good question. @DaGaMs Can you provide a sample repo so we can clone and verify the issue on our machines locally?

[bbito]

@DaGaMs , The way I see it, you have a Date in JavaScript as in:
var nowDate = new Date(); -> Mon Jul 25 2016 17:05:03 GMT-0700 (Pacific Standard Time).

If you want to send that to a REST API like Loopback you are going to turn it into JSON:
nowDate.toJSON(); -> 2016-07-26T00:05:03.861Z

If you retrieve it from Loopback and make a new JavaScript Date object it will be the same date you put in:
var backFromJSON = new Date('2016-07-26T00:05:03.861Z'); ->Mon Jul 25 2016 17:05:03 GMT-0700 (Pacific Standard Time)

[DaGaMs]

@bbito @superkhau Indeed, I get different results depending whether I get the date with mysqljs or with loopback. Here's a code snippet to explain what I did (from a mocha/chai test):

// This is our direct connection to the database
var db = mysql.createConnection({
  host     : 'localhost',
  user     : 'test_user',
  password : 'test_password',
  database : 'testdb',
  dateStrings: false
});

// imagine a db.connect(...) somewhere here, and all the server bootstrapping

// make a request object
request = require('supertest')('http://localhost:3000');

thisDate = new Date();
db.query("INSERT INTO `File` (created, name) VALUES (?, ?, ?)", [thisDate, "testFile.txt"], (err, res) => {
  db.query("SELECT * FROM `File`", function (err, rows, fields) {
    // Get the file through the api
    request.get('/api/Files')
    .expect(200)
    .expect('Content-Type', /json/).end((err, res) => {
      var files = res.body;
      var file = files[0];

      var createdDate = new Date(file.created);
      console.log("ThisDate", thisDate);
      console.log("ThisDate toJSON", thisDate.toJSON());
      console.log("MysqlJS toJSON", rows[0].created.toJSON());
      console.log("MysqlJS", rows[0].created);
      console.log("LoopBack", file.created);
      console.log("CreatedDate toJSON", createdDate.toJSON());

      done(err);
    });
  });
});

The result looked like this:

ThisDate Tue Jul 26 2016 13:49:04 GMT+0100 (BST)
ThisDate toJSON 2016-07-26T12:49:04.039Z
MysqlJS toJSON 2016-07-26T12:49:04.000Z
MysqlJS Tue Jul 26 2016 13:49:04 GMT+0100 (BST)
LoopBack 2016-07-26T13:49:04.000Z
CreatedDate toJSON 2016-07-26T13:49:04.000Z

I'm in London, but due to summer time I'm in GMT+1. So, while mysqlJS correctly converts UTC back to local time, loopback ignores the local timezone.

[bbito]

@DaGaMs - that sure is confusing!
What does your loopback /common/models/file.json model definition look like?
..and what does the value look like in mysql?
mysql> select * from File;

[bbito]

When I PUT your ThisDate toJSON value ("2016-07-26T12:49:04.039Z") through loopback API Explorer it persists unchanged in mysql (less ms):

mysql> select mft_datetime, mft_date_time_notes from date_time_table where id=12;
+---------------------+-----------------------------------------+
| mft_datetime        | mft_date_time_notes                     |
+---------------------+-----------------------------------------+
| 2016-07-26 12:49:04 | from DaGaMs as 2016-07-26T12:49:04.039Z |
+---------------------+-----------------------------------------+

When I retrieve it through loopback: http://192.168.0.184:3000/api/DateTimeTables/12
It is still the same UTC date (less ms):

{
  "id": 12,
  "mftDateTimeNotes": "from DaGaMs as 2016-07-26T12:49:04.039Z",
  "mftDatetime": "2016-07-26T12:49:04.000Z"
}

[DaGaMs]

My File.json file looks like this:

{
  "name": "File",
  "base": "PersistedModel",
  "strict": true,
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "mysql": {
    "schema": "testdb",
    "table": "File"
  },
  "properties": {
    "created": {
      "type": "date",
      "required": true,
      "length": null,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "created",
        "dataType": "datetime",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    },
    "path": {
      "type": "string",
      "required": true,
      "length": 4096,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "path",
        "dataType": "varchar",
        "dataLength": 4096,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

the datasource.json file is pretty simple:

{
  "db": {
    "name": "db",
    "connector": "memory"
  },
  "mysql": {
    "host": "localhost",
    "port": 3306,
    "database": "testdb",
    "password": "test_password",
    "name": "mysql",
    "user": "test_user",
    "connector": "mysql"
  }
}

[DaGaMs]

The File table is generated during migration, so I'm not doing anything weird in mysql. describe File gives me this:

mysql> describe File;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| created     | datetime      | NO   |     | NULL    |                |
| path        | varchar(4096) | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

I ran the script again, getting these dates:

ThisDate Tue Jul 26 2016 18:09:17 GMT+0100 (BST)
LoopBack 2016-07-26T18:09:18.000Z

Now, here's what's in the database:

mysql> select * from File;
+----+---------------------+---------------------------------+
| id | created             | path                            |
+----+---------------------+---------------------------------+
|  1 | 2016-07-26 18:09:18 | /some/path/to/file/testFile.txt |
+----+---------------------+---------------------------------+

So, what seems to be happening is that when I INSERT the row into the database using the mysqlJS adapter, the date gets saved in local time. That's in line with the mysql documentation as far as I can tell. However, loopback seems to assume that whatever comes out of the database is in UTC.

Looking at the output of the console.logs earlier, the most obvious explanation for this is that loopback doesn't use toJSON but somehow converts the Date object returned by mysqlJS into a string and back into a date, dropping the timezone information...

[bbito]

Very interesting!
Although loopback checks the timezone config setting:
https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L114
...it doesn't seem to do anything with that except make sure it is passed on to mysqljs and just converts to UTC regardless:
https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L352

  if (prop.type === Date) {
    if (!val.toUTCString) {
      val = new Date(val);
    }
    return dateToMysql(val);
  }

https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L307

function dateToMysql(val) {
  return val.getUTCFullYear() + '-' +
    fillZeros(val.getUTCMonth() + 1) + '-' +
    fillZeros(val.getUTCDate()) + ' ' +
    fillZeros(val.getUTCHours()) + ':' +
    fillZeros(val.getUTCMinutes()) + ':' +
    fillZeros(val.getUTCSeconds());

  function fillZeros(v) {
    return v < 10 ? '0' + v : v;
  }
}

It seems that the mysqljs timezone setting is checked and passed to the connection, but dateToMysql is called to make a UTC Date regardless and I don't understand why toJSON isn't simply called if that is the intent anyway...

[bbito]

I don't know what it breaks, but if I remove that call to dateToMysql(val), then the DATETIME is stored as local (as expected).
https://github.com/bbito/loopback-connector-mysql/blob/2.x-latest/lib/mysql.js#L298
Here's the diff link:
strongloop:loopback-2.x...bbito:2.x-latest
[Edited: Was modifying Master, Now basing off 2.x-latest which matches my loopback install]

When I send the same data as above it now gets stored as:

mysql> select mft_datetime, mft_date_time_notes from date_time_table where id=13;
+---------------------+-----------------------------------------+
| mft_datetime        | mft_date_time_notes                     |
+---------------------+-----------------------------------------+
| 2016-07-26 05:49:04 | from DaGaMs as 2016-07-26T12:49:04.039Z |
+---------------------+-----------------------------------------+

The return through loopback is still the same as well.