Sequelize查询以查找所有在日期范围和时间范围之间的记录

Question

我有一个类型为 Sequelize.DATE 的列 time_scheduled，用于存储 事件 的计划时间。

我试图创建一个 Sequelize 查询，从中可以找到落在日期范围和时间范围内的所有 事件。同样的 SQL 查询如下所示。

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';

相应的输出结果：

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

- Arunsridher

Answer 1

你可以使用Op.between与Sequelize.where和Sequelize.cast一起实现你想要的功能：

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')
]
  }
});

- Anatoly