mysql - 如何将当前时间与其他已有的4个时间字段进行比较

Question

我有一个包含 4 个时间的表，分别是 bus_start_time1、bus_end_time1、start_time_2 和 end_time2。现在我的要求是构建一个与当前时间相比介于 start_time1 和 end_time1 或 start_time2 和 end_time2 之间的查询。

这是我的 table :

+------------+-------------+-----------+-------------+-----------+
| vendor_id  | start_time1 | end_time1 | start_time2 | end_time2 |
+------------+-------------+-----------+-------------+-----------+
| 5000788324 | 09:00 AM    | 11:00 AM  | 02:00 PM    | 06:00 PM  | 
| 5000815782 | 10:00 AM    | 01:00 PM  | 02:00 PM    | 10:00 PM  | 
| 5000482136 | 09:00 AM    | 01:00 PM  | 02:00 PM    | 09:00 PM  | 
| 6786788131 | 09:00 AM    | 11:00 AM  | 02:00 PM    | 06:00 PM  | 
+------------+-------------+-----------+-------------+-----------+

我尝试了以下查询，但它对我不起作用:

select count(ven_availability_id) as count from vendor_del_avilability vda where vda.vendor_id = ? and ((vda.bus_start_time1 >  DATE_FORMAT(now(), '%T')  and vda.bus_end_time1 >  DATE_FORMAT(now(), '%T')) or  (vda.bus_start_time2 >  DATE_FORMAT(now(), '%T')  and vda.bus_end_time2 >  DATE_FORMAT(now(), '%T')));

请帮帮我。提前致谢。

Answer 1

试试这个:

SELECT count(vendor_id) as count
FROM vendor_del_avilability
WHERE vendor_id = ?
AND (
    NOW() BETWEEN
    (STR_TO_DATE(CONCAT(CURDATE(), ' ', start_time1), '%Y-%m-%d %h:%i%p'))
    AND 
    (STR_TO_DATE(CONCAT(CURDATE(), ' ', end_time1), '%Y-%m-%d %h:%i%p'))
)
OR
(
    NOW() BETWEEN
    (STR_TO_DATE(CONCAT(CURDATE(), ' ', start_time2), '%Y-%m-%d %h:%i%p')) 
    AND
    (STR_TO_DATE(CONCAT(CURDATE(), ' ', end_time2), '%Y-%m-%d %h:%i%p'))
);