Sequelize reads datetime in UTC only

Question

I have set the timezone option to my timezone (Europe/Zagreb and I've tried with +02:00 too), and the time is saved as it should be, however, when reading the time from the database, Sequelize converts it to UTC. I have tried different timezones too, each is saved correctly, but always converted to UTC when read from database.

Am I doing something wrong or is this a known issue and are there any workarounds?

I create a new connection with:

sequelize = new Sequelize(config.database, config.username, config.password, config);

and my configuration looks something like this:

"development": {
    "username": "root",
    "password": "root",
    "database": "db",
    "host": "localhost",
    "dialect": "mysql",
    "timezone": "Europe/Zagreb"
}

Domiciliate

Answer 1

You can Try This code, I had the same problem and this worked for me.

const sequelize = new Sequelize(mysql.database, mysql.user, mysql.password, {
    host: mysql.host,
    port:3306,
    dialect:'mysql',
    define: {
      underscored: true,
      freezeTableName: true, //use singular table name
      timestamps: false,  // I do not want timestamp fields by default
    },
    dialectOptions: {
      useUTC: false, //for reading from database
      dateStrings: true,
      typeCast: function (field, next) { // for reading from database
        if (field.type === 'DATETIME') {
          return field.string()
        }
          return next()
        },
    },
    timezone: '+01:00'
});

Manual

Thanks, just the typeCast function in dialectOptions did the trick in my case. – Domiciliate

I cannot get this method to work. Sequelize is still using UTC when inserting and reading the data. It will only work locally but not when I upload my app to my server. My server is set to use my timezone and the MySQL server is set to use the system timezone. Any thoughts as to why this will not work? – Yoruba

@Yoruba the timezone : '+01:00' // use your timezone here is used for writing to the database, check if it is writing correct value in the dateTime field, and if it is writing correct value then just use the typeCast function in dialectOptions. – Manual

What if it is not writing the correct value? I've set the timezone to use timezone: '-07:00' and even America/Boise. Neither of those work. – Yoruba

@Yoruba typecast function just return the written value in the table as a string , so if the written value is incorrect then typecast function will just return that same value, it will not convert it into your timezone. So to make it work correctly you have to make sure that you are writing the correct time (as per your timezone) in the table. – Manual

I understand the concept, which is why I am trying to solve the problem as to why the incorrect time zone is being written to the database. It seems as though my configuration is being ignore by sequelize. At this point I'm just going to leave it as is and not use sequelize in the future. – Yoruba

@ArpitPandey Can you share documentation link for dialectOptions. I am not able to find all options in Sequelize Usage guide – Hierarchize

@M.AtifRiaz dialectOtions are part of mysql (or xyz DB), here you go github.com/mysqljs/mysql – Manual

Answer 2

Maybe timezone is not needed if you use UTC.

For example, my mysql server is timezone '+08:00',

"mysql2": "^1.6.4", "sequelize": "^4.41.2"

const sequelize = new Sequelize(database, username, password, {
    host: hostname,
    port: port,
    dialect: 'mysql',
    dialectOptions: {
        typeCast: function (field, next) {
            if (field.type == 'DATETIME' || field.type == 'TIMESTAMP') {
                return new Date(field.string() + 'Z');
            }
            return next();
        }
    },
    operatorsAliases: false,
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    }
});

Vulgarity

Answer 3

You can try this option, that worked for me:

const sequelize = new Sequelize(database, username, password, {
  host: hostname,
  port: port,
  dialect: 'mysql',
  dialectOptions: {
    encrypt: false,
    options: {
      useUTC: false, // for reading from database
    },
  },
  operatorsAliases: false,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

Diocesan

Answer 4

I had the same problem and this worked for me. My time zone is +7:00

var con = mysql.createConnection({
    host: "localhost",
    port: "3306",
    user: "root",
    password: "123456",
    insecureAuth : true,
    database: "devices",
    timezone: ".007Z"//change utc to +7
});

Coworker