Bookshelf.js/Knex.js is too "helpful" with UTC DATETIME columns

Question

I have a MySQL table, and this table has a DATETIME column named datetime_utc. It is, as you might expect, a date and time in UTC. In my Bookshelf models, I have defined a virtual getter that converts this into ISO 8601 string format using Moment.js. My model looks something like this:

bookshelf.plugin('virtuals');

exports.MyModel = bookshelf.Model.extend({
    tableName : 'my_table',
    idAttribute : 'id',
    virtuals : {
        datetime_iso : {
            get : function () {
                return moment.utc(this.get('datetime_utc')).format();
            }
        }
    }
});

Problem is, when Bookshelf (or the underlying Knex that powers it) sees the DATETIME column, it wraps the value in a new Date(...) before giving it to my code. Since the date's value is in UTC, but the Date constructor assumes the value to be in the server's local non-UTC timezone, I end up with a Date object that has the right date in the wrong timezone. Once Moment starts working on this Date, all the values are off by a fixed number of hours.

I worked around this by looking for Date objects, and decomposing the date components directly into the Moment constructor. But it feels gross:

get : function () {
    var dt = this.get('datetime_utc');

    if (dt instanceof Date) {
        dt = [
            dt.getFullYear(), dt.getMonth(), dt.getDate(),
            dt.getHours(), dt.getMinutes(), dt.getSeconds()
        ];
    }

    return moment.utc(dt).format();
}

Is there a cleaner way to either get a non-wrapped YYYY-MM-DD HH:MM:SS string value from Bookshelf, or a shorthand to create a new Moment object from a Date with the timezone ignored/munged to UTC?

Answer 1

Turns out this is not caused by either Knex or Bookshelf, but rather the underlying node-mysql library. There is a connection property called timezone that will be appended to each DATETIME, DATE, TIMESTAMP, and NEWDATE value before it is parsed into a Date object.

Knex will pass this property through to node-mysql while it is initializing:

require('knex')({
    "client": "mysql",
    "connection": {
        "host": "...",
        "user": "...",
        "password": "...",
        "database": "...",
        "timezone": "UTC"    <-- This is the culprit
    }
});