Gotcha! Timezones in nodejs and mysql

This tutorial is basically about avoiding all the time-related gotchas when using nodejs and mysql together. If you know of any other cases, please leave a comment.

First of all, ditch the mysql2 package. It doesn’t handle timezones well (which is a shame, because it has a nice promise interface):

Edit: The issues in mysql2 seems to be fixed :)

timezone pain · Issue #642 · sidorares/node-mysql2

The package you want is mysql (though I recommend using something like Knex to get a nice interface).

DATE and DATETIME fields

In mysql, DATE and DATETIME are stored as simple strings. They are also sent and received as strings, without any regard for timezones.

The mysql package, however, will by default try to convert these types into Date objects. This is pretty much just wrong since DATETIME and DATE don’t have timezones, whereas Date objects do.

The mysql package will by default:

• Interpret DATE as new Date(‘YYYY-MM-DD’) , which is equivalent to new Date(‘YYYY-MM-DD 00:00:00+00:00’). In other words, the result will be shifted when displayed in your current timezone. Depending on what timezone you’re in, you may get the wrong date.
• Interpret DATETIME as new Date(‘YYYY-MM-DD HH:mm:ss’). This may be correct if the one who stored the DATETIME is in the same timezone as yourself.

The correct thing to do in most cases is to either use a timestamp field if you care about timezones, or if you don’t care about timezones just use simple strings.

You can ask the mysql package to return DATETIME and DATE as strings with the dateStrings option:

let config = {
    // ...
    dateStrings: [
        'DATE',
        'DATETIME'
    ]
}
let pool = mysql.createPool(config)

TIMESTAMP fields

In mysql, timestamps are stored as actual timestamps, but are received as strings. The timezone will depend on the timezone of your connection to mysql.

The mysql package will by default assume that the mysql server is running in the same timezone as the nodejs server, and interpret the TIMESTAMP as:

new Date(‘YYYY-MM-DD HH:mm:ss’)

This is a very dangerous assumption that will come back to bite you sooner or later.

We can resolve the issue by:

1. Setting the timezone of the mysql session
2. Configure the mysql package to expect the timezone we just configured

The config looks like this:

let config = {
    // ...
    timezone: 'UTC',
}
let pool = mysql.createPool(config)
pool.on('connection', conn => {
    conn.query("SET time_zone='+00:00';", error => {
        if(error){
            throw error
        }
    })
})

I should also point out that using UTC and +00:00 is a nice solution no matter what timezone you’re in, since the mysql package will now give you a Date object interpreted like this:

new Date(‘YYYY-MM-DD HH:mm:ss UTC’)

Configuration summary

The total configuration looks something like this:

let config = {
    // ...
    timezone: 'UTC',
    dateStrings: [
        'DATE',
        'DATETIME'
    ]
}

let pool = mysql.createPool(config)
pool.on('connection', conn => {
    conn.query("SET time_zone='+00:00';", error => {
        if(error){
            throw error
        }
    })
})

MySQL gotchas

This has nothing to do with nodejs, but gotchas are so much fun, right? :D

Did you know that the order you define timestamps in is important? Compare these tables:

CREATE TABLE test1(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(20),
  ts1 TIMESTAMP NOT NULL,
  ts2 TIMESTAMP NOT NULL,
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE test2(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(20),
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts1 TIMESTAMP NOT NULL,
  ts2 TIMESTAMP NOT NULL
);

They’re excactly the same, right? Well, if you have ‘explicit_defaults_for_timestamp’ enabled, they are.

If not, mysql will add the following to the first timestamp in the table declaration (but only the first, and only if it doesn’t set a default itself):

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

So test2 works as expected, while test1 has some nicely hidden config.

More info here: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html