Sequelize Where statement with date

I am using Sequelize as my backend ORM. Now I wish to do some WHERE operations on a Date.

More specifically, I want to get all data where a date is between now and 7 days ago.

The problem is that the documentation does not specify which operations you can do on Datatypes.DATE

Can anyone point me in the right direction?


Just like Molda says, you can use $gt, $lt, $gte or $lte with a date:

model.findAll({
  where: {
    start_datetime: {
      $gte: moment().subtract(7, 'days').toDate()
    }
  }
})

If you're using v5 of Sequelize, you've to include Op because the key was moved into Symbol

const { Op } = require('sequelize')

model.findAll({
  where: {
    start_datetime: {
      [Op.gte]: moment().subtract(7, 'days').toDate()
    }
  }
})

See more sequelize documentation here


I had to import the Operators symbols from sequelize and use like so.

const { Op } = require('sequelize')

model.findAll({
  where: {
    start_datetime: {
      [Op.gte]: moment().subtract(7, 'days').toDate()
    }
  }
})

According to the docs, for security reasons this is considered best practise.

See http://docs.sequelizejs.com/manual/tutorial/querying.html for more info.

Using Sequelize without any aliases improves security. Some frameworks automatically parse user input into js objects and if you fail to sanitize your input it might be possible to inject an Object with string operators to Sequelize.

(...)

For better security it is highly advised to use Sequelize.Op and not depend on any string alias at all. You can limit alias your application will need by setting operatorsAliases option, remember to sanitize user input especially when you are directly passing them to Sequelize methods.


You can also use Sequelize.literal() to perform dates manipulation in SQL.

The following code works with Postgres, but I'm quite sure something similar could be done in other systems as well:

model.findAll({
  where: {
    start_datetime: {
      $gte: Sequelize.literal('NOW() - INTERVAL \'7d\''),
    }
  }
})