sequelize: MSSQL does not respect timezone setting when parsing dates

Hi,

I am experiencing an issue with dates.

I use the following declaration for my table

  var p_usrnot = sequelize.define("usrnot", {
    usrnotincde : {
      type : DataTypes.INTEGER,
      allowNull : true,
      primaryKey : true,
      autoIncrement : true
    },
    usrincde : {
      type : DataTypes.INTEGER,
      allowNull : false,
      defaultValue : "0"
    },
    subjectdsc : {
      type : DataTypes.STRING,
      allowNull : true
    },
    msgdsc : {
      type : DataTypes.STRING,
      allowNull : true
    },
    replydsc : {
      type : DataTypes.STRING,
      allowNull : true
    },
    notnatincde : {
      type : DataTypes.INTEGER,
      allowNull : false,
      defaultValue : "1"
    },
    usrnoticon : {
      type : DataTypes.STRING,
      allowNull : true
    },
    usrnotcolor : {
      type : DataTypes.STRING,
      allowNull : true
    },
    ackpnt:{
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue : true
    },
    ackreadpnt:{
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue : true
    },
    rcdreadhrd : {
      type : DataTypes.DATE,
      allowNull : true
    },
    rcdreceivehrd : {
      type : DataTypes.DATE,
      allowNull : true
    },
    rcdcreatehrd : {
      type : DataTypes.DATE,
      allowNull : true
    },
    operctcincde : {
      type : DataTypes.INTEGER,
      allowNull : false
    }

I have configured the timezone property with +01:00 I tried to set defaultValue: sequelize.Sequelize.NOW to my rcdcreatehrd but at each time I try to insert a date, it is setted like this : 2016-02-11 17:54:37.0930000 +00:00 and so when I try to make a new Date of this, I get :Thu Feb 11 2016 18:54:37 GMT+0100 (CET), so my date is one hour later than the real hour

I tried removing the timezone, send new Date().toJSON(), new Date().toUTCString()…

What can I do ?

I am using mssql with DATETIMEOFFSET (but tried DATETIME2 too without success).

Thanks

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 20 (10 by maintainers)

Most upvoted comments

¯\_(ツ)_/¯ I’m using sequelize": "^3.27.0, there’s a public API to achieve this behavior? (I tried to find without success)

https://github.com/sequelize/sequelize/blob/v3.27.0/lib/dialects/mssql/connection-manager.js#L30

brunoventura on Dec 7, 2016

After some hours debugging sequelize I’ve found a solution to problem with timezone:

options.timezone = "America/Sao_Paulo"

db.connectionManager.$refreshTypeParser({
    types: {
        mssql: [111]
    },
    parse: value => moment(value).tz('UTC').format('YYYY-MM-DD HH:mm:ss.SSS')
});

Any regrets with this solution?

brunoventura on Dec 7, 2016

PR welcome, the relevant code is here (needs to add a parse function) https://github.com/sequelize/sequelize/blob/v3.27.0/lib/dialects/mssql/data-types.js#L102

janmeier on Dec 13, 2016

The problem is that datetime in mssql does not support specifying the timezone, see https://msdn.microsoft.com/en-us/library/bb677335.aspx

The insert looks correct - what sequelize actually inserts is a date without timezone, but sequelize knows that it is in CET, and should parse it correctly when fetching again.

I just tested this, and it does indeed seem that the mssql dialect does not parse the date with the correct timezone

janmeier on Feb 12, 2016