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?

回答1:

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

回答2:

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.... :)

回答3:

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()