DATE type casting

[MattMur]

DATE type casting should be turned off due to incorrect formatting it receives when it gets converted back a string. It should return from MYSQL as YYYY-MM-DD, however after type casting is shows as: yyyy-MM-ddTHH:mm:ss.000Z. If you store a field as DATE, why would you want an arbitrary time appended?

[dougwilson]

Hi, what version of the mysql library are you using? The current alpha release no longer formats dates like that, but in the correct MySQL format of YYYY-MM-DD HH:mm:ss. Also note that JavaScript does not have any way to differentiate between a date and a date + time, as they are both stored in a Date object. Sending the time at the end of a date to a DATE column will cause MySQL to just drop the time component, just storing the date part as you would intend.

[CraigLRussell]

Seems to me that the default type cast for TIME and DATE database types should be JavaScript string.

And the default type cast for TIMESTAMP and DATETIME database types should be JavaScript Date.

It would be nice if the encoder would recognize fractional seconds and properly encode milliseconds into the JavaScript Date.

[MattMur]

I was using alpha8. Agreed with Craig. It seems to me it would be best if TIME and DATE simply returned as a string.

[dougwilson]

It would be nice if the encoder would recognize fractional seconds and properly encode milliseconds into the JavaScript Date.

When MySQL returns a date time string that includes fractional seconds, 2.0.0-alpha9 will include these in the Date object created.

[elgs]

I also get something strange like this 2014-03-11T06:56:21.000Z. Can you just make it return whatever mysql returns? Yet not to convert it to the GMT.

[dougwilson]

Can you just make it return whatever mysql returns?

You can do that by setting dateStrings: true in your configuration and get all dates and times back as strings instead of Date objects.

[elgs]

@dougwilson Thank you so much! That's exactly what I expected.

[habahut]

@dougwilson Can you elaborate on how to set the dateStrings: true configuration variable? I'm not seeing any examples in the readme for this. Thanks

[elgs]

@habahut, see if the follow code is helpful to you or not.

(function () {
    "use strict";

    var config = {
        "host": "server_ip",
        "port": 3306,
        "user": "username",
        "password": "password",
        "connectionLimit": 20,
        "dateStrings": true
    };

    var mysql = require('mysql');
    exports.pool = mysql.createPool(config);
})();

[sidorares]

@habahut it is in the readme:

dateStrings: Force date types (TIMESTAMP, DATETIME, DATE) to be returned as strings rather then inflated into JavaScript Date objects. (Default: false)

var db = mysql.createConnection({ user: 'foo', password: 'bar', dateStrings: true });

[habahut]

@elgs @sidorares having looked again I see it right there in the readme. Sorry about that, I'm not sure how I missed it before. Thanks for the quick response.

[SystemParadox]

@dougwilson dateStrings: true is all very well, but that also affects TIMESTAMP, which does not seem desirable.

I agree with @CraigLRussell. I would expect the following behaviour:

• TIME and DATE should always return strings - these types should never, ever be affected by timezone. JavaScript's Date object just can't be trusted with them. This should return strings even with dateStrings: false.
• TIMESTAMP represents a specific instant in time (i.e. unix timestamp relative to UTC) and thus requires whatever timezone handling is necessary to allow Date objects both in and out. Possibly this should still return Date objects even with dateStrings: true (possible a separate timestampStrings option would be best).
• DATETIME - not sure about this one. Sometimes these are used like TIMESTAMP, sometimes like DATE+TIME. This is what dateStrings should specify.

The current behaviour really needs to change. DATE and TIME are unusable with dateStrings: false, and yet this is the default?

[SystemParadox]

@dougwilson any thoughts on this?

I have run into problems with this so many times it's not funny. DATE fields absolutely MUST NOT use a timezone-aware format. It is far too fragile.

If the client/server use different timezones, one creates a date object with a time of midnight, sends it over the wire, it gets converted to the other timezone - if you're lucky it'll be 01:00, if you're unlucky it'll be 23:00 and now your date is wrong.

Worse, this will also break if the host environments use a differing timezone libraries, which is just horrific. Try 1970-06-28 with your timezone set to Europe/London on Linux and Windows and you will get different results.

I don't want to set dateStrings: true because that loses the timezone information from DATETIME and TIMESTAMP which need it. The workaround is to use a custom typeCast function:

var db = mysql.createConnection({
    typeCast: function (field, next) {
        if (field.type == 'DATE') {
            return field.string();
        }
        return next();
    }
})

[dougwilson]

We can do this in 3.0, which does not have any target yet. It's the reason I haven't closed the issue :)

[ravi]

Is the implication in one of the comments true -- namely, node-mysql converts datetimes to GMT/UTC automatically? It seems to be happening in my case but I want to be sure. This is a bad thing because my datetimes are already GMT/UTC in the DB and are getting converted somewhere to GMT/UTC assuming that they are in the local timezone.

[dougwilson]

Hi @ravi , it is converted based on what you set in your timezone option in your connections settings (https://github.com/felixge/node-mysql#connection-options). If your database is using UTC, then you should be setting timezone: "+0000" in your options.

[ravi]

@dougwilson thank you. I also found from another issue reported here that I can set process.env.TZ, which also does the trick for me.

[bbito]

I have made a PR, #1481 , that hopes to address DATE typecasting by allowing a string value of "date" for the dateStrings config setting which only effects DATE columns while allowing typecasting to JS Date objects for DATETIME and TIMESTAMP columns. I am hoping for a little help on why the PR might be failing the Travis CI for Node.js: 0.12: https://travis-ci.org/mysqljs/mysql/jobs/146421528#L325
I have reviewed the test-connect-timeout.js and I don't see any clues as to why this PR might effect the outcome of that test. Could someone more familiar with the Travis tests for this project give me any insight?