Automatic date storing / inflation has issues around timezones

[iarna]

In some circumstances a mismatch in timezones between Node and MySQL can result in incorrect date handling.

First, node-mysql always uses the system timezone when storing and inflating dates, due, I'm sure, to the limitations in JavaScript's date objects.

Second, MySQL has a somewhat schizophrenic relationship with timezones. It has one that it tracks to decide how to return TIMESTAMP columns (which are stored in UTC but displayed based on the value of time_zone). By contrast, DATEs and DATETIMEs are stored without a timezone and its up to the application to manage them. Best practices is to use store with UTC and convert to the local timezone either with CONVERT_TZ or in the application.

This means that if my database stores its dates in UTC, I have to:

1. Always convert my Date objects into strings before storing them.
2. Always use CONVERT_TZ to convert them back into whatever timezone Node is using so that they're inflated properly.

It's 2 that I'm concerned about.

I think that in the case of date/time related data, there should be an option to get it back as a string just like there is with bignums. This is for the same reason– MySQL date/time's don't fully map to any JavaScript datatype. As such, I'd like an option to opt out of the lossy date and datetime conversion.

[dougwilson]

This library defaults to local time zone intentionally, as other language's MySQL libraries do as well, to match the MySQL server default of local time zone. This works out-of-the-box if both the client and server are in the same time zone. When you use this library, you should set the timezone option to be the time zone your MySQL server is set up to. This makes the date inflation/deflation works just fine.

So if your server is configured so the default connection time zone is UTC, then just pass timezone: 'Z' to the constructor and then it should work correctly.

You also have the option to "opt out" of the datetime conversion just by setting up your own type cast function:

var connection = mysql.createConnection({
  ...,
  typeCast: function (field, next) {
    if (field.type === 'DATE' || field.type === 'DATETIME' || field.type === 'TIMESTAMP' || field.type === 'NEWDATE') {
      return field.string();
    }
    return next();
  }
});

You can also pass the typeCast to individual query calls if you only want to change the typecast on certain queries.

[iarna]

I missed the timezone connect option. Thank you.

As far as the typeCast argument goes, that's very clearly called out as experimental in the documentation and I wouldn't want to rely on it. "It is not recommended (and may go away / change in the future) to disable type casting, but you can currently do so"