New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DATE returned as raw string if timezone is set (MySQL) #4208

Closed
balintbako opened this Issue Jul 29, 2015 · 6 comments

Comments

4 participants
@balintbako

balintbako commented Jul 29, 2015

We are using MySQL and sequelize 3.2.0 (the issue exists in 3.5.2 as well).
The model is the following:

module.exports = function(sequelize, DataTypes) {
    var SequelizeTest = sequelize.define('SequelizeTest', {
        id : {
            type : DataTypes.INTEGER,
            primaryKey : true,
            autoIncrement : true
        },
        testDate: DataTypes.DATE

    }, {
        tableName : 'tgb_sequelize_test',
        timestamps : true
    });
    return SequelizeTest;
};

Running the same call with and without a "timezone" setting in data.json give different "testDate". It returns a Date object if "timezone" is not set but it returns a string when it is set.

models.SequelizeTest.findAll().then(function(testItemsResp){
    for(var i = 0; i < testItemsResp.length; i++){
        console.log(testItemsResp[i].testDate.getHours());
    }
}).catch(function(err){
    console.log(err);
}); 

This is the data.json in use (with "timezone"):

"dev": {
    "username": "...",
    "password": "...",
    "database": "...",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "timezone": "Europe/London"
}

For some reason the SELECT query seems to be handled as "raw" so the date parsing doesn't take place.

@janmeier janmeier added the bug label Jul 29, 2015

@janmeier

This comment has been minimized.

Member

janmeier commented Jul 29, 2015

Note to self - fix this in #4186

Right now the date parsing happens in node-mysql which uses native dates, and new Date('2015-07-29 15:22:52 Europe/London') gives Invalid Date. We should take over this parsing and use moment instead

For now you can fix this by using timezone offset instead

@janmeier janmeier self-assigned this Jul 29, 2015

@balintbako

This comment has been minimized.

balintbako commented Jul 29, 2015

Thank you for the feedback!

Only thing is that timezone offset doesn't handle Daylight Saving Time (BST for London) so we had to change the offset to +01:00 at the beginning to BST and then to +00:00 at the end of it.

@janmeier

This comment has been minimized.

Member

janmeier commented Jul 29, 2015

I know, and I very much hope to have that PR merged before the next DST change ;). It was merely offered as a temporary fix

@BridgeAR

This comment has been minimized.

Contributor

BridgeAR commented Jul 29, 2015

@janmeier it would also be nice to support the postgres special values :)

@janmeier

This comment has been minimized.

Member

janmeier commented Jul 29, 2015

@BridgeAR Postgres can keep those in the DB! If someone wants that kind of magic, they can use moment :)

@tipztek

This comment has been minimized.

tipztek commented Mar 21, 2016

I use the follow config with System:

{
    "development": {
        "username": "username",
        "password": "password",
        "database": "db",
        "host": "127.0.0.1",
        "dialect": "mysql",
        "logging" : false,
        "timezone" : "System",
        "define" : {
            "underscored" : true,
            "timestamps" : false
        }
    }
}

and all of my dates are now "Invalid Date".

This appears to be in >= 3.19.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment