mysql - AM/PM 不能在 sql 中使用

Question

我有这个名为 task 的表:

id     t_started_on              t_due_on
------------------------------------------------    
 1    2018-01-12 10:45 AM    2018-01-12 07:45 PM
 2    2018-01-18 09:15 AM    2018-01-19 07:00 PM
 3    2018-01-16 02:30 AM    2018-01-22 10:30 AM

我想选择今天日期介于带有 AM 或 PM 的两个日期字段之间的记录。没有 AM/PM 我知道该怎么做，但我的 AM/PM 要求在那里:

除了日期比较，时间部分本身也应该在开始日期的时间部分和截止日期的时间部分之间。

我该怎么做？

如果当前时间戳是 '2018-01-19 03:56 PM' 而不是数据库中的上述数据，结果应该只有一行(第 2^nd 行)，但我是在结果中得到两行(也是第 3^rd 行)。

这是我的 SQL 查询

SELECT * 
FROM `task` 
WHERE  '2018-01-19 03:56 PM' between t_started_on and t_due_on

不应包含第 3^rd 行，因为截止日期的时间部分早于当前时间的时间部分。

Answer 1

您最好将日期存储为 DATETIME。由于您拥有这些值，它已经表明您的数据库字段不是 date 类型，因为将此类字符串插入 date 字段不会产生错误。

如果无法更改，则可以使用 STR_TO_DATE 函数:

SELECT * 
FROM `task` 
WHERE t_status !=3 
AND   STR_TO_DATE('2018-01-19 03:56 PM', '%Y-%m-%d %h:%i %p') BETWEEN
        STR_TO_DATE(t_started_on, '%Y-%m-%d %h:%i %p') AND 
        STR_TO_DATE(t_due_on, '%Y-%m-%d %h:%i %p')

但这确实是一个劣等的解决方案。

不过，您期望只得到一行似乎很奇怪。当然第三行符合要求，因为开始/截止日期在您检查的日期之前/之后，与时间部分无关。

附录

在评论中你说你想应用不同的逻辑。我猜测您想分别比较日期和时间组件，并且两者都符合between 条件。这真的应该在问题中解释，因为目前没有指定。

在这种情况下，您可以使用 DATE_FORMAT 仅提取时间部分，并用它重复条件:

SELECT *,
       STR_TO_DATE(t_started_on, '%Y-%m-%d %h:%i %p'),
       STR_TO_DATE(t_due_on, '%Y-%m-%d %h:%i %p'),
       STR_TO_DATE('2018-01-19 03:56 PM', '%Y-%m-%d %h:%i %p')
FROM `task` 
WHERE t_status !=3 
AND   STR_TO_DATE('2018-01-19 03:56 PM', '%Y-%m-%d %h:%i %p') BETWEEN
        STR_TO_DATE(t_started_on, '%Y-%m-%d %h:%i %p') AND 
        STR_TO_DATE(t_due_on, '%Y-%m-%d %h:%i %p')
AND   DATE_FORMAT(STR_TO_DATE('2018-01-19 03:56 PM', '%Y-%m-%d %h:%i %p'), '%h:%i') BETWEEN
        DATE_FORMAT(STR_TO_DATE(t_started_on, '%Y-%m-%d %h:%i %p'), '%h:%i') AND 
        DATE_FORMAT(STR_TO_DATE(t_due_on, '%Y-%m-%d %h:%i %p'), '%h:%i')

此查询将从结果中排除第 3^rd 行。