mysql - Sequelize Query to find all records that falls in between date range

Question

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]

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

question from:https://stackoverflow.com/questions/43115151/sequelize-query-to-find-all-records-that-falls-in-between-date-range

Answer 1

The solution which works for me is this:-

# here startDate and endDate are Javascript Date object
const where = {
    from: {
        $between: [startDate, endDate]
    }
};

For reference to know more about operators:- http://docs.sequelizejs.com/en/latest/docs/querying/#operators

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