Sequelize set timezone and datetime format for mysql

When you use Sequelize ORM to connect database then you might face issue time wrong time retrival from database. Because Sequelize returns UTC in select queries for datetime fileds. It this situation you need to change the configuration of Sequelize to get the proper records. 

You retrieve your timezone based datetime you need to set following in Sequelize connection:

dialectOptions: {
    useUTC: false, //for reading from database
    dateStrings: true,
    typeCast: true
},
timezone: '+05:30' //for writing to database

Full connection is as below: 

connect(conf = config.database.mysql){
    if(!connection) connection = new Sequelize(conf.name, conf.user, conf.password, {
      host: conf.host,
      port: conf.port,
      dialect: 'mysql',
      charset: 'utf8',
      collate: 'utf8_general_ci',
      query: {
        raw: true
      },
      logging:true,
      dialectOptions: {
            useUTC: false, //for reading from database
            dateStrings: true,
            typeCast: true
      },
      timezone: '+05:30' //for writing to database
    });
  }

Now you will get the Datetime format: YYYY-mm-dd h:i:s

Before dialectOptions Output:

"added_on": "2018-10-18T06:45:38.000Z",
 "updated_on": "2018-10-18T06:46:13.000Z",

After dialectOptions New Output Format:

"added_on": "2018-10-19 01:08:50",
"updated_on": "2018-10-19 01:08:50",


Keywords: