Sequelize Query 查找日期范围内的所有记录

2022-01-19 00:00:00 mysql sequelize.js

我有一个带有列的模型:

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

我想查询 from 或 to 介于日期范围之间的所有记录:[startDate, endDate]

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

试过类似的东西:

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

类似的东西:

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

推荐答案

适合我的解决方案是:-

The solution which works for me is this:-

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

更多关于运营商的参考:- http://docs.sequelizejs.com/en/latest/docs/querying/#operators

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

注意:在MYSQL中，between比较运算符是inclusive，这意味着它等价于表达式(startDate <= from AND from <= endDate).

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

相关文章