How to handle MySQL Date and Time formats in Sequelize?

I’ve been playing with Sequelize for a while and one thing that I really didn't like about it is that how it deals with MySQL dates. It requires that fields of Date or Time types would be defined as default JavaScript date objects. This in my opinion causes some unnecessary problems as you need to do conversations between date strings in different formats...

For example we have two fields in MySQL:

`CreateDate` DATE NULL DEFAULT NULL
`CreateTime` TIME NULL DEFAULT NULL

And I need to create new entry and show it to the user

let now = new Date() // 2017-02-27T10:03:30.212Z

DB.SomeModel.create({
    CreateDate: now,
    CreateTime: now
}).then(function(created) {
    console.log(created.CreateDate) // 2017-02-27T10:03:30.212Z
    console.log(created.CreateTime) // 2017-02-27T10:03:30.212Z
})
  • The first thing you will notice is that all dates are in JS format, not like default MySQL dates 2017-02-27 12:03:30. Yes, on the next query you will get it, but for now sorry.
  • Second thing is that all those dates are in UTC. It's not bad, but in my case I needed it with exact time zone

To solve the first problem just add get methods to the fields. We will use Moment for date parsing.

CreateDate: {
    type: DataTypes.DATEONLY,
    get: function() {
        return moment.utc(this.getDataValue('CreateDate')).format('YYYY-MM-DD')
    }
},
CreateTime: {
    type: DataTypes.TIME,
    get: function() {
        let time = this.getDataValue('CreateTime')

        if (moment(time, moment.ISO_8601, true).isValid()) {
            return moment(this.getDataValue('CreateTime')).format('HH:mm:ss')
        } else {
            return time
        }
    }
}

To solve the second problem you will need to set timezone for both Sequalize and Moment

const timezone = 'Europe/Vilnius'

require('moment').tz.setDefault(timezone)

let sequelize = new Sequelize(
    'database',
    'username',
    'password',
    {
        timezone: timezone
    }
)

Read More / Comment

Fork me on GitHub