CODEWITHSUNDEEP
sequelize.js
wondim
wondim

Reputation: 727

Sequelize converts UTC time to wrong local zone

I have a table that stores datetime with time zone in UTC zone. It is stored in PostgreSQL. The data is in the table is not converted to any zone which is desirable. However, when I query using findAll method, it returns the result as time zone +5 than -5 (which is the correct server zone). I kept the default useUTC to true without setting true or false as true is the default. Below is an example:

Database record "2020-10-09T03:49:28.652Z"

Sequelize response "2020-10-09T08:49:28.652Z"

Expected response "2020-10-09T22:49:28.652Z"

I am using this workaround for the time being. Note: that this is run in ExtJS 6.2 Chart axes renderer function.

renderer: function (axis, date_str, layoutContext, lastLabelText) {
   var d = new Date(date_str.replace('Z', '').replace('T', ' ') + ' UTC');
   d.setHours(d.getHours() - 5);
   return Ext.Date.format(new Date(d), 'H:i:s');

My worry is that Sequlize uses user timezone than server timezone. Also, this timezone might not be correct during daylight saving.

Is there any good fix for sequelize and the render function above?

Edit:

I have added the findAll raw SQL query from the log:

SELECT "id", "capture_datetime", "pco2", "createdAt", "updatedAt" FROM "bos_pco2" AS "bos_pco2" WHERE "bos_pco2"."capture_datetime" IS NOT NULL ORDER BY "bos_pco2"."capture_datetime" DESC LIMIT 30;

Capture date_time is a column a Date column with UTC date and time. The other raw SELECT query works now when UTC datetime is supplied.

This Raw select query is not working too. In PG Admin, I get a result but no result with sequelize.

SELECT * FROM bos_chl_a_pc_pe WHERE capture_datetime 
        BETWEEN '2020-10-10T22:41:44.000Z'::TIMESTAMP and '2020-10-11T00:41:44.045Z'::TIMESTAMP 
        ORDER BY capture_datetime;

Upvotes: 0

Views: 2200

Answers (1)

doublesharp
doublesharp

Reputation: 27607

By default Sequelize will use the timezone of the system that it is running on and convert dates returned to that timezone format. If you want to force Sequelize to use UTC dates (and not observe DST) then you will need to specify the timezone option when you create the Sequelize connection.

const instance = new Sequelize(
  databaseName,
  userName,
  userPassword,
  {
    host: databaseHost,
    dialect: 'postgres',
    // specify to use UTC and the timezone here
    dialectOptions: {
      useUTC: true, // tell the driver you want to use UTC
      timezone: 'Etc/GMT0', // this works for me with mariadb
    },
    timezone: 'Etc/GMT0', // the github issue indicates the TZ here for postgres
  }
);

See this Github issue for more info: https://github.com/sequelize/sequelize/issues/854

Upvotes: 2

Related Questions