CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Manish Mishra
Manish Mishra

Reputation: 12375

Sequelize date comparison is inserting wrong time

Sequelize automatically inserting wrong timestamp.

somewhere in my code I am doing below:

fromdate = moment(fromdate).format('YYYY-MM-DD'); 
todate = moment(todate).add(1, 'days').format('YYYY-MM-DD');
whereclause['order_date'] = { [Op.between]: [fromdate, todate] };

and the SQL query that gets generated is this:

`MyTable`.`mydatetimecolumn` BETWEEN '2020-06-18 04:00:00' AND '2020-06-19 04:00:00';

where did this 04:00:00 came from?

column is defined like below in its entity:

mydatetimecolumn: {
            type: DataTypes.DATE
        }

I am using sequelize 5.21.2 with mysql. I am stumped at what might be going wrong. Though it seems, sequelize is auto inserting timezone hours, but why, how to turn it off or provide something else?

even if I do .format('YYYY-MM-DD 00:00:00'), it still overrides it to 4:00:00.

Upvotes: 2

Views: 3287

Answers (1)

jnovack
jnovack

Reputation: 8807

"where did this 04:00:00 came from?", it came from YOUR timezone. Your server must be set to GMT-4. mysql is using GMT, but your script/os is using GMT-4. It's "correct" (allowing for different interpretations of "correct").

Try setting the timezone within the connection.

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: '-04:00'
});

Upvotes: 3

Related Questions