CODEWITHSUNDEEP
mysqlsequelize.js
Anonymous Zombie
Anonymous Zombie

Reputation: 889

Sequelize query to find all records that falls in between date range

I have a model with columns:

from: { type: Sequelize.DATE }
to: { type: Sequelize.DATE }

I want to query all records whose either from OR to falls in between the date ranges: [startDate, endDate]

I tried something like:

const where = {
    $or: [{
        from: {
            $lte: startDate,
            $gte: endDate,
        },
        to: {
            $lte: startDate,
            $gte: endDate,
        },
    }],
};

Something like:

SELECT * from MyTable WHERE (startDate <= from <= endDate) OR (startDate <= to <= endDate

Upvotes: 69

Views: 140544

Answers (4)

Akshay Pratap Singh
Akshay Pratap Singh

Reputation: 3327

The solution which works for me is this:

// Here startDate and endDate are Date objects
const where = {
    from: {
        $between: [startDate, endDate]
    }
};

For reference to know more about operators: Querying, Operators

Note:

In MySQL, the between comparison operator is inclusive, which means it is equivalent to the expression (startDate <= from AND from <= endDate).

Upvotes: 92

bahri noredine
bahri noredine

Reputation: 751

Step 1

Require the Op from SEQUELIZE:

const { Op } = require('sequelize');

Step 2

Declare two constants:

const startedDate = new Date("2020-12-12 00:00:00");
const endDate = new Date("2020-12-26 00:00:00");

Step 3

table.findAll({where : {"fieldOfYourDate" : {[Op.between] : [startedDate , endDate ]}}})
.then((result) =>  res.status(200).json({data : result}))
.catch((error) =>  res.status(404).json({errorInfo: error}))

Upvotes: 13

M.A.K. Ripon
M.A.K. Ripon

Reputation: 2148

Try this condition. What I think you are asking will do so.

For a new version of Sequelize:

const where = {
    [Op.or]: [{
        from: {
            [Op.between]: [startDate, endDate]
        }
    }, {
        to: {
            [Op.between]: [startDate, endDate]
        }
    }]
};

OR as your code structure:

const where = {
    $or: [{
        from: {
            $between: [startDate, endDate]
        }
    }, {
        to: {
            $between: [startDate, endDate]
        }
    }]
};

For more information, you can follow this Sequelize official documentation.

Upvotes: 38

defraggled
defraggled

Reputation: 1218

I agree with @VigneshSundaramoorthy's comment that even if [Op.between] works with date strings, the type definitions suggest this usage isn't officially supported. The operation wants array of numbers, and at least as at 28 Dec 2021, the documentation has no examples of using Op.between for a date range (only for number ranges).

However, we can achieve the same result in a type-compliant way, which also aligns with the Sequelize documentation:

const where = {
  "date_field": {
    [Op.and]: {
      [Op.gte]: startOfDateRange,
      [Op.lte]: endOfDateRange
    }
  }
}

This has also has the slight advantage of being more explicit (that the search is inclusive) rather than relying on dialect behaviour for BETWEEN.

Upvotes: 7

Related Questions