时间戳

首页 > 解决方案 > Mysql-cast 和 convert in join 不起作用

mysql - Mysql-cast 和 convert in join 不起作用

问题描述

在 MySQL 表中，它们存储return_time为 VARCHAR(20) 和return_date日期字段。现在我必须根据给定的日期和时间进行比较并产生结果。例如，保存在时间列中的记录将像“10:00 PM”、“11:00 PM”、“02:00 AM”等，而保存在日期列中的记录将像“2021-02-02”、“2021-” 01-02'等

我结合了return_dateand return_time，并将其与给定的日期和时间进行比较，例如

 SELECT * 
 FROM `travel_infos` as TravelInfo 
 WHERE TravelInfo.travel_status = 'Open' AND  
      cast(concat(TravelInfo.return_date, ' ',  STR_TO_DATE(TravelInfo.return_time, '%l:%i %p' )) as datetime) > '2021-03-03 14:03'

它向我输出 231 条记录。travel_infos表有 700 条记录。

然后我在不考虑return_time列的情况下进行连接

SELECT Part.*,TravelInfo.*,Category.*,Buttons.* 
FROM travel_infos As TravelInfo 
LEFT JOIN  parts as Part ON Part.id = TravelInfo.part_id_1 
LEFT JOIN buttons as Buttons ON Part.button_name_id= Buttons.id 
LEFT JOIN categories as Category ON Part.category_id = Category.id 
WHERE Part.id != 0 AND TravelInfo.id!=0 
      AND Part.part_type IN('Charter') AND Part.store_id='1' 
      AND TravelInfo.travel_status = 'Open' 
      AND TravelInfo.return_date >= '2021-03-03 14:03' 
      AND Part.is_delete=0

它输出了 175 条记录。

所以现在我想通过同时考虑return_date和来加入return_time。所以我试着像

    SELECT Part.*,TravelInfo.*,Category.*,Buttons.* 
    FROM travel_infos As TravelInfo 
    LEFT JOIN parts as Part ON Part.id = TravelInfo.part_id_1 
    LEFT JOIN buttons as Buttons ON Part.button_name_id= Buttons.id 
    LEFT JOIN categories as Category ON Part.category_id = Category.id 
    WHERE Part.id != 0 AND
          TravelInfo.id!=0 
          AND Part.part_type IN('Charter') 
          AND Part.store_id='1' 
          AND TravelInfo.travel_status = 'Open' 
          AND (cast(concat(TravelInfo.return_date, ' ', 
              STR_TO_DATE(TravelInfo.return_time, '%l:%i %p' )) as datetime)) >= '2021-03-03 14:03' AND Part.is_delete=0

这会输出 0 条记录。第三个 SQL 查询出了什么问题？第 1 和第 2 都产生输出。在我的第三个查询中，我修改了第二个查询，WHERE就像在第一个查询中一样。

有人可以帮我解决这个问题吗谢谢。

标签： mysqlsql

解决方案

将您要比较的两个日期都转换为 where 子句中的日期时间：

SELECT Part.*,TravelInfo.*,Category.*,Buttons.* 
FROM travel_infos As TravelInfo 
LEFT JOIN parts as Part ON Part.id = TravelInfo.part_id_1 
LEFT JOIN buttons as Buttons ON Part.button_name_id= Buttons.id 
LEFT JOIN categories as Category ON Part.category_id = Category.id 
WHERE Part.id != 0 AND
  TravelInfo.id!=0 
  AND Part.part_type IN('Charter') 
  AND Part.store_id='1' 
  AND TravelInfo.travel_status = 'Open' 
  AND TravelInfo.return_date >= STR_TO_DATE('2021-03-03', '%Y-%m-%d')
  AND cast(TravelInfo.return_time as time) >= cast('14:03:00' as time) 
  AND Part.is_delete=0

推荐阅读