Sequelize query - compare dates in two columns

Question

I have a table in Sequelize with two date columns - i.e. with :

var Visit = sequelize.define("Visit", {
  /*...*/
  scheduleEndDate: {
    type: DataTypes.DATE
  }
  actualEndDate: {
    type: DataTypes.DATE
  }
  /*...*/
});

I want to make a query that returns rows where actualEndDate is before scheduleEndDate - and can't get the format right. What I've tried for the where part of my findAll query is:

where: { actualEndDate: { lt: Visit.scheduleEndDate } }

- throws an error because Visit not defined (have also tried with this.scheduleEndDate - also throws an error)

where: { actualEndDate: '< scheduleEndDate' }

- does a string comparison of actualEndDate against the string '< scheduleEndDate'

Do I need to define an instance method to do the date comparison / how best to solve?

Answer 1

This should be possible with

where: { actualEndDate: { $lt: sequelize.col('scheduleEndDate') } }

Answer 2

In lastest version (v5) you need to use Sequelize.Op. Docs

const Op = Sequelize.Op;
...
where: {
    actualEndDate: {
        [Op.lt]: sequelize.col('scheduleEndDate')
    }
}

All operations:

Project.findAll({
  where: {
    id: {
      [Op.and]: {a: 5},           // AND (a = 5)
      [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR a = 6)
      [Op.gt]: 6,                // id > 6
      [Op.gte]: 6,               // id >= 6
      [Op.lt]: 10,               // id < 10
      [Op.lte]: 10,              // id <= 10
      [Op.ne]: 20,               // id != 20
      [Op.between]: [6, 10],     // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
      [Op.in]: [1, 2],           // IN [1, 2]
      [Op.notIn]: [1, 2],        // NOT IN [1, 2]
      [Op.like]: '%hat',         // LIKE '%hat'
      [Op.notLike]: '%hat',       // NOT LIKE '%hat'
      [Op.iLike]: '%hat',         // ILIKE '%hat' (case insensitive)  (PG only)
      [Op.notILike]: '%hat',      // NOT ILIKE '%hat'  (PG only)
      [Op.overlap]: [1, 2],       // && [1, 2] (PG array overlap operator)
      [Op.contains]: [1, 2],      // @> [1, 2] (PG array contains operator)
      [Op.contained]: [1, 2],     // <@ [1, 2] (PG array contained by operator)
      [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
    },
    status: {
      [Op.not]: false           // status NOT FALSE
    }
  }
})