node-mysql2: Timezone is wrong on client when reading records from SQL server running in UTC time

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.

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

In my not at all humble opinion it is a terrible mistake for a developer to rely on MySQL default time/timezone behavior. Their are too many ways for it to go wrong and in nearly every case it introduces silent errors that occur infrequently that the developer will most likely not be aware of.

MySQL is fundamentally flawed in that it allows inserting a time without a well defined timezone. I.E. the database “guesses” what timezone you meant instead of forcing you to send the timezone with the time.

For example, during daylight savings transition you have a repeat of one hour of local time, and the timezone on client/server silently changes on you, could be DST or not.

So it’s pretty easy to accidentally insert a date that is one hour off, and if the clocks on server and client are not synced well, you could even end up inserting a date 2 hours off in unusual edge cases.

Javascript has it’s own issues too which makes this even worse. As far as I know their is no guarantee in the language spec that the local date functions Date.getHours(), Date.getTimezoneOffset(), and others will work atomically and use the same calculated timezone offset during DST changes. So their is no practical way to extract local date & time & timezone in a deterministic way.

As such, I think all the Date.getXXX() functions are unwise to use in the real world - they are not reliable.

When storing date/times I use something similar to the following to avoid bad timezone behavior: JS: dateField = dateField.getTime() / 1000; SQL: INSERT INTO foo (dateField) VALUES (FROM_UNIXTIME(?));

When retrieving date/times I use the following: SQL: SELECT UNIX_TIMESTAMP(dateField) as dateField FROM foo; JS: dateField = new Date(dateField * 1000);

It would not be unreasonable for the library print an error advising the developer they are doing something bad when encountering a date/time field being sent or received to MySQL.

But I guess you could use the Date.getUTCXXX() functions to output the time and force the timezone to UTC on each connection if you want to be nicer.

To go further down the rabbit hole, I don’t think most people should store information in DATE/DATETIME/TIMESTAMP fields at all.

What’s with the stupid 0000-00-00 00:00:00 timestamp. Why do we need a zero time? I have no idea. Should be NULL. Causes me nothing but problems.

DATE & DATETIME only go back to the year 1000, so no good for historical dates, they use more storage than timestamp, and are slow to convert to epoch time, which is the only time which MySQL, operating systems, and most programming languages agree upon.

TIMESTAMP is 32 bit unix epoch time in disguise, and runs out into big problems in 2038, and doesn’t support dates before 1970.

None of them support millisecond or smaller units.

Presumably when TIMESTAMP runs out of storage MySQL will switch to a 64 bit format.

So why not use 64 bit numbers today? If you store milliseconds since epoch in a SIGNED BIGINT, it is deterministic, easy to parse with lots of programming languages, easy to convert to date formats in MySQL using FROM_UNIXTIME(field / 1000), you can store dates that are hundreds of thousands of years old, can work on it with integer math, and it fits in the registers on 64 bit cpus which are now the norm. You won’t be suffering when TIMESTAMP runs out of range in less than 20 years.

I worked around this by returning unix_timestamp(date_field) which is always in UTC, and in the client turning it back into a Javascript date, which displays using the client’s timezone.

@sidorares I’m having similar issue. In database, datetime was stored in UTC+0, but datetime returned by node-mysql2 marks that time according to host machine’s timezone.

For example, if I stores 10:00:00 UTC+0 to mysql, say node is deployed to machine with timezone: GMT+8, theDate object is returned as 10:00:00 GMT+8.

Question, how do I force to parse in UTC format?

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

For anyone who cannot / does not want to change their SQL or other code, here is an example of fixing with a connection-level typeCast. Connection config before:

{
    host: 'localhost',
    user: 'example_user',
    port: '3306',
    password: secrets.db_password,
    database: 'example_db'
}

Connection config after:

{
    host: 'localhost',
    user: 'example_user',
    port: '3306',
    password: secrets.db_password,
    database: 'example_db',
    typeCast(field, next) {
        if(field.type == 'DATETIME') {
            const utcTime = Math.floor((new Date(field.string() + " UTC")).getTime() / 1000);
            const fixedDate = new Date(0);
            fixedDate.setUTCSeconds(utcTime);
            return fixedDate;
        }
        return next();
    }
}

Hopefully the priority of the missing timezone option gets moved up soon, because otherwise anyone migrating from a rails-generated database will find that all of their created_at and updated_at fields return off times.

Well, i think this issues relates with this and the fix provided was using datestring param like, mysql.createConnection({ user: 'foo', password: 'bar', dateStrings: true });

correct me if i am wrong !

@AlexLeung 640kb should be enough for everybody

I may take you up on that offer for a PR. I’ll have some time in 2 weeks to do that.

I’m keen to have this behave similar to mysqljs/mysql so that in the future you should be able to use timezone parameter

Question, how do I force to parse in UTC format?

hi @roylee0704 , right now the only option is to pass typeCast function as a connection or query parameter and do something similar to what mysqljs/mysql does here. Note that currently timezone is not passed from config to typeCast, you’ll have to hard code it or use closure variable