MySQL 是日期范围之间的日期

Question

我有一个包含房间开始和结束日期的表格。我写了这个SQL:

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.end BETWEEN '2022-12-01' AND '2022-12-31'

房间在这些日期之间处于事件状态。但是，当我这样过滤时，

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.end BETWEEN '2022-12-01' AND '2022-12-30'

我没有结果。这些日期就像一个数组:

dates = ['2017-01-01', '2017-01-02', '2017-01-03', ... , '2022-12-30', '2022-12-31']

所以，您可以看到该房间在 2022 年 12 月 30 日处于事件状态，但我无法获取它。我怎样才能做到这一点？

希望我能很好地表达自己。

Answer 1

如果您希望行在给定日期处于事件状态，请使用以下逻辑:

WHERE pccr.deleted_at IS NULL AND
      pccr.end >= $date AND
      pccr.start <= $date

如果您有日期范围，请使用:

WHERE pccr.deleted_at IS NULL AND
      pccr.end >= $start_date AND
      pccr.start <= $end_date