Mysql: timezone error

[buddy-yao]

1. the time returned was correct.
   
2. the time returned [ RowDataPacket { '@@global.time_zone': '+08:00', '@@session.time_zone': '+08:00', 'now()': 2017-03-24T03:03:14.000Z } ] was wrong.

import mysql from 'mysql';
import Connection from 'mysql/lib/Connection';
import Promise from 'bluebird';
import config from './config';

Promise.promisifyAll([Connection], {multiArgs: false});
const conn = mysql.createConnection({
  host     : config.mysql_addr,
  port     : config.mysql_port,
  user     : config.mysql_user,
  password : config.mysql_passwd,
  database : config.mysql_db,
  timezone : '+08:00'
});
conn.connect();

conn.queryAsync('select @@global.time_zone, @@session.time_zone, now();').then((data) => {
  console.log(data);
});
export default conn;

1. when i change the timezone to 08:00, then the returned time was correct.
   

   I'm puzzled now .... who can help me? Thanks!

[dougwilson]

Hi @buddy-yao I happen to have a MySQL server on the +08:00 timezone and I ran your script with timezone set to both '+08:00' and '08:00' and the result for both was the same and what you're expecting.

Perhaps the difference here is the versions of the various systems, so can you provide all the following?

1. The exact version of MySQL server you are using.
2. The exact version of this module you are using.
3. The exact version of Node.js you are using.
4. The time zone offset the machine you are running the Node.js script on is set to.

Thanks!

[buddy-yao]

1. mysql server version: 5.5 from docker image mysql:5.5
2. module version: 2.13.0
3. node.js version: 6.10.0
4. timezone offset: Asia/Shanghai (CST, +0800) output by timedatectl command.
   also i set a enviroment variable: TZ=CST before running the script.
   if u need more, please @me

[buddy-yao]

i run it on ubuntu 16.04.

[bbito]

@buddy-yao The behavior looks correct to me. I think you are finding that the serialized date does not match the date returned from MySQL command line client. That is to be expected as the serialized representation calls toJSON() on the date object which internally uses toISOString(). If that serialized date is used to create a new javascript Date object on a client in the same time zone as the server, the date will match what you see from your direct query to MySQL.
Perhaps a fiddle will help:
http://jsfiddle.net/buckbito/qpfxv01q/10/
[edit: updated fiddle link with Asia/Shanghai date output]
Date.prototype.toJSON(): https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toJSON

[buddy-yao]

@bbito Thank you very much! But I have to invoke toLocalString() method manually. So, what's the usage of connection option timezone? Is it useless?

[dougwilson]

Hi @buddy-yao I guess what @bbito said explains why I wasn't seeing your issue: you were actually referring to the way in which JavaScript stringifies dates, nothing this module does. The point of the timezone option is because the JavaScript Date objects understand the offset the date belongs to (for .toLocalString() to function for example), and the MySQL protocol responses include no time zone information. Thus the timezone option is how you tell this module what time zone those are in so it can then construct the Date object correctly.

[buddy-yao]

thanks

[Gpia]

I had this problem too, so what are the conclusions and solutions?