CODEWITHSUNDEEP
javascriptmysqlsqlnode.jssequelize.js
Preston Wallace
Preston Wallace

Reputation: 77

How to get current date/time using database timezone in sequelize

I need to get the current time, according to the database timezone (not my local timezone, and not default UTC timezone), using Sequelize. Is there a Sequelize method to do this?

My database is in Eastern time, and when I query the db directly SELECT CURRENT_TIMESTAMP; it returns the date/time in Eastern time (which is correct).

But when I query via Sequelize in Node const [[{time}]] = await db.sequelize.query('SELECT CURRENT_TIMESTAMP AS time'); it returns the date/time in UTC.

Two problems:

1 - I would prefer using a Sequelize method instead of a raw query.

2 - This still doesn't get me the result I want. The time needs to be Eastern.

This is my DB setup:

const sequelize = new Sequelize(dbUrl, {
  dialectOptions: {
    useUTC: false // for reading from database
  },
  timezone: '-04:00', // for writing to database
  define: {
    charset: 'utf8'
  }
})

As mentioned above, when I query using the above queries, the date is always returned in UTC, which I did not expect, given I said useUTC: false. How do I get it in Eastern time (the database timezone)?

Upvotes: 0

Views: 3646

Answers (2)

Sanoodia
Sanoodia

Reputation: 925

Add useUTC property in your dialectOptions like this

dialectOptions: {
    encrypt: false ,
    options: {
        useUTC: false, // for reading from database
    },
},

Upvotes: 0

KenOn10
KenOn10

Reputation: 1968

  1. I'm not aware of a sequelize method like getCurrentDate().

  2. The UTC conversion problem seems to bite everyone (myself included). Here are some details. Not sure if dialectOptions: {useUTC: false }, has any function at all - just adding the typeCast method solved the problem for me.

      dialectOptions: {
    typeCast: function (field, next) { // for reading from database
      if (field.type === 'DATETIME') {
        return field.string()
      }
      return next()
  },

The result can be used for a new js Date object: 

const sql = 'select current_timestamp';
my_app.my_DB.query(sql, {raw: true, type: Sequelize.QueryTypes.SELECT})
    .then(data => {
        console.log(data[0].current_timestamp);
        let d1 = new Date(data[0].current_timestamp);
    });

This works fine for me - but make sure to test thoroughly!

Upvotes: 1

Related Questions