CODEWITHSUNDEEP
node.jsmomentjssequelize.js
jabbermonkey
jabbermonkey

Reputation: 1820

UTC DATE in Sequelize.js

I'm using Sequelize in node.js to save open and close times. I'm also using moment.js to format. Alter a FindOrCreate I'm doing this:

result.open = moment(hours.open, "hh:mma").format("YYYY-MM-DD HH:mm:ss");
result.save()
...

This works fine and the time gets formatted to MySQL's datetime format. The Problem is when I retrieve the time Seqquelize thinks it's a UTC time and converts it to EST (my server timezone).

I would prefer it to go into the database as UTC and come out the same way. Is there something I'm doing wrong? Why does Sequelize not convert it to UTC on insert but assumes it's UTC coming out? ALso, is there a way to not have it try to convert to my server timezone?

Upvotes: 9

Views: 28323

Answers (4)

Sanoodia
Sanoodia

Reputation: 905

Ist way.

go to data-types of mssql in node module

...\node_modules\sequelize\lib\dialects\mssql\data-types.js

then modify your DateType value to your current utc like this

 class DATEONLY extends BaseTypes.DATEONLY {
    static parse(value) {
      return moment.utc(value).format('YYYY-MM-DD');
    }
  }

2nd Way(Recommended).

you can add useUTC property in Sequelize connection

const sequelize = new Sequelize({
    ......
    dialectOptions: {
        encrypt: false ,
        options: {
          useUTC: false, // for reading from database
          requestTimeout: 90000
        },
    },
    })

Upvotes: 0

DavidA
DavidA

Reputation: 628

I had the same problem. i've fixed it like this:

config/sequelize.js:

const sequelize = new Sequelize(database, user, password, {
host,
dialect: 'mysql',
port,
operatorsAliases,
dialectOptions: {
    useUTC: true, // -->Add this line. for reading from database
},
timezone: '+02:00', // -->Add this line. for writing to database
pool: {
    max: 10,
    min: 0,
    idle: 10000,
},
logging: console.log,
// define: {},
})

momentJS (UTC): 

const ACCEPT_FORMAT = 'YYYY-MM-DD hh:mm:ss'
const { start_date, end_date } = req.params
const start = moment.utc(start_date, ACCEPT_FORMAT)
const end = moment.utc(end_date, ACCEPT_FORMAT)
console.log(start)
console.log(end)

Hope it will help someone.... :)

Upvotes: 9

ygaradon
ygaradon

Reputation: 2298

I faced the same issue, you are using moment wrong. You should use moment.utc instead of just moment .

Take a look at the moment documentation: http://momentjs.com/docs/#/parsing/utc/

result.open = moment.utc(hours.open, "hh:mma").format("YYYY-MM-DD HH:mm:ss");
result.save()

Upvotes: 4

Abdo
Abdo

Reputation: 14051

I know this is pretty late but here it is for those struggling with this with postgres (maybe this can point you in the right direction for other engines)

As you know postgres stores datetimes in UTC.

The issue, for me, turned out to be not in Sequelize but rather in the pg package.

In order to fix it, place this before your sequelize = new Sequelize() line

var types = require('pg').types;
var timestampOID = 1114;
types.setTypeParser(1114, function(stringValue) {
  return new Date( Date.parse(stringValue + "0000") );
});

The problem, I think, is that the pg package is doing new Date(stringValue), which returns the date in the server's timezone, which is wrong (unless it's in utc itself)

For more info, please refer to this thread: https://github.com/brianc/node-postgres/issues/429

Upvotes: 7

Related Questions