Timezone is wrong on client when reading records from SQL server running in UTC time

[jasonhillier]

Using mysql2 library on my machine running in Pacific time, the records, when deserialized from SQL response, seem to be applying local timezone offset but still keeping date object marked at UTC. This means the timezone offset is actually applied backwards:

Time in db record (SQL server set to UTC): 2016-02-01 21:39:46
Time returned by mysql2 (running on a machine in Pacific TZ): 2016-02-02T05:39:46.000Z (this is a UTC date in future, which is wrong)
Time returned by mysql2 when running on a machine with TZ set to UTC: 2016-02-01T21:39:46.000Z (this is correct time)

Expected result: I would expect toISOString() to return the same UTC date value regardless of client TZ, when reading the same record from the same server.

I believe this code is responsible for inserting SQL's UTC date into a JS object which is instantiated in local time:

Packet.prototype.readDateTime: ... return new Date(y, m-1, d, H, M, S, ms);

[sidorares]

Server does not store TZ value in the data row, the only information we have at the time we receive row is "2016-02-01 21:39:46"

Also the client does not now which TZ server is running.

Expected result: I would expect toISOString() to return the same UTC date value regardless of client TZ, when reading the same record from the same server.

I need to double check if this is supported or not, but the only solution I know is to pass TZ to a client, I think this is what node-mysql is doing. This way you do "this client would treat dates as TZ, because I know that server has it's timezone set TZ"

[sidorares]

see 'timezone' option in https://github.com/felixge/node-mysql#connection-options

[jasonhillier]

Hi sidorares,

I tried setting the 'timezone' option on the SQL connection string, it didn't make a difference.

MySQL does have a way of querying it to find out the SQL connection session's timezone:

More info here:
http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql

[sidorares]

yes, you can get offset by doing SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) but I don't want all clients to perform this query at connection time just in case this information is needed later.

I tried setting the 'timezone' option on the SQL connection string, it didn't make a difference.

@jasonhillier - looks like this functionality is missing, I'll try to add it

@dougwilson , you did a lot of tz -related refactoring in node-mysql, what do you think about this idea: if timezone client parameter is set to 'auto', ( or maybe 'server' ) then do SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) query as first command automatically and apply this value to all received dates

[codecvlt]

Setting the timezone option in mysql2 to "utc", I would expect date objects returned to have a UTC timezone set. Doing this work post query seems like a hack, so I would definitely love to see this resolved.

+1

[dougwilson]

then do SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) query as first command automatically and apply this value to all received dates

The problem is when a connection exists during a daylight time change, the offset would then be wrong until the connection is re-created.

In addition, I think this is starting to move from a basic driver module that just does whatever commands the user sends to more of a ORM, which I think should be built as a different module on top of this module. What this module does is actually exactly what the C mysql command line client is doing in regards to time zone handling. If only JavaScript had a richer Date object that could represent "unknown timezone", that would have been best.

I think the issue is packet.readDateTime is always using:

return new Date(y, m-1, d, H, M, S, ms);

when called from:

(function(){ return function BinaryRow(packet) {

this interprets the mysql date/time as local regardless of the global/session timezone of the server.

[sidorares]

@embedthis main problem is that 1) DateTime packet does not have timezone 2) JS does not have built-in "Date without known TZ" object. As a result, this library builds local Date leaving conversion to user

But currently it is not symmetrical. Put one date in and you get a different value back. If you leave it up to the user, it should not get changed on only one side ie. changed on read but not on write.

[rickyk586]

Given that you can do dateStrings: true in the connection options, I think the conversion is ok to leave to the user. However I think it would be very helpful to have this in the documentation somewhere.

Yes, the user can do it. The problem is that the code currently is inconsistent and not symmetrical. It converts on only one side.

[sidorares]

I'm a bit lost. Can you provide simple code examples to highlight problems @embedthis ? I'm keen to improve this, and if it requires major/minor version bumps it's good time to do now before 1.0.0 release

[rickyk586]

The timezone of the date (whatever the server is set to) is lost when you insert it into the DB. The only way to pull the date and set it back to the original timezone is to explicitly tell the mysql2 library which timezone to convert all dates to, and this would require the addition of an extra timezone library (if done with javascript). There's also the option to set the actual connection timezone, so maybe that's an option: SET time_zone = 'UTC'; https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
I'm guessing the latter is how https://github.com/mysqljs/mysql does it.

[sidorares]

I don't think https://github.com/mysqljs/mysql sets server time, there is only option to tell client what's your local timezone is so server DATETIME columns are converted into js Date according to tz offset from option

[rickyk586]

Yeah that's what I meant, running SET time_zone = 'UTC'; affects the timezone for just that one client's connection. Could be a solution for @embedthis or others.