CODEWITHSUNDEEP
javascriptnode.jssequelize.js
Marc Rasmussen
Marc Rasmussen

Reputation: 20565

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?

Upvotes: 106

Views: 165901

Answers (5)

brianandere
brianandere

Reputation: 931

Based on the other answers and comments, a way without using third party packages would be:

const sevenDaysFromNowResults = await db.ModelName.findAll({
      where: {
        createdAt: {
          [Sequelize.Op.gte]: new Date(new Date() - (7 * 24 * 60 * 60 * 1000)) // seven days ago
        }
      }
    })

The seven days are calculated using Date utility in milliseconds (days * 24hrs * 60mins * 60secs * 1000ms)

Upvotes: 3

Michael Yaworski
Michael Yaworski

Reputation: 13483

This solution is without the moment.js library.

Between 7 days ago and now

const sevenDaysAgo = new Date(new Date().setDate(new Date().getDate() - 7));
models.instagram.findAll({
  where: {
    my_date: {
      $gt: sevenDaysAgo,
      $lt: new Date(),
    },
  },
});

Between now and 7 days from now

const sevenDaysFromNow = new Date(new Date().setDate(new Date().getDate() + 7));
models.instagram.findAll({
  where: {
    my_date: {
      $gt: new Date(),
      $lt: sevenDaysFromNow,
    },
  },
});

Notes:

  • $gt stands for "greater than". You could use $gte instead of $gt. $gte stands for "greater than or equal to". Same for $lte of course.
  • Technically speaking, you need both $lt and $gt to make sure that the date isn't into the future (per the original question).
  • Other answers show the use of [Sequelize.Op.gt] instead of $gt. Use that if on Sequelize v5.

Upvotes: 19

marcopeg
marcopeg

Reputation: 1238

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\''),
    }
  }
})

Upvotes: 21

Evan Siroky
Evan Siroky

Reputation: 9438

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

Upvotes: 135

James Graham
James Graham

Reputation: 751

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.

Upvotes: 75

Related Questions