Sequelize query to find all records that fall between a date range and time range-CodePudding

I have a column time_scheduled of type Sequelize.DATE which stores the scheduled time of events.

I am tying to create a Sequelize query where I can find all events that fall between a date range and time range. The SQL query for the same is below.


SELECT * FROM events where time_scheduled between '2021/10/01' and '2021/10/10' and time_scheduled::timestamp::time between '12:00' and '15:00';

Sample output for the same

`id`	`time_scheduled`
`4d543320-4d23-46d2-8a54-fbb13a1251d0`	`2021-10-05 14:30:00 00`
`d6640e70-f873-436c-a59d-5a4c4fc655b7`	`2021-10-06 12:02:49.441 00`
`b11481b2-ffdd-413e-81af-f83df756bcc9`	`2021-10-06 13:55:36.62 00`
`53447517-f226-407f-94f4-63ddc8c17d9c`	`2021-10-07 13:59:48.123 00`
`f0344678-11eb-4422-9d23-43e8d5320f55`	`2021-10-07 14:14:13.647 00`

CodePudding user response：

You can use Op.between along with Sequelize.where and Sequelize.cast to achieve what you want:

const records = await Events.findAll({
  where: {
    [Op.and]: [{
      time_scheduled: {
        [Op.between]: ['2021/10/01', '2021/10/10']
      }
    }, 
    Sequelize.where(Sequelize.cast(Sequelize.col('time_scheduled'), 'time'), '>=', '12:00'),
    Sequelize.where(Sequelize.cast(Sequelize.col('time_scheduled'), 'time'), '<=', '15:00')
]
  }
});