AM/PM 不能在 sql 之间使用 [英] AM/PM not working using between in sql

问题描述

我有一张名为 task 的表:

I have this table called 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 要求在那里:

I want to select records where today's date is between the two date fields with AM or PM. Without AM/PM I know how to do it, but my AM/PM requirement is there:

除了日期比较之外，时间部分也应该自己落在开始日期的时间部分和到期日期的时间部分之间.

Apart from the date comparison, also the time part should on its own fall between the time part of the start date and the time part of the due date.

我该怎么做?

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

If current time stamp is '2018-01-19 03:56 PM' than with above data in the dabase, the results should have one row only (2^nd row), but I am getting two rows in the results (also the 3^rd row).

这是我的 SQL 查询

Here is my SQL Query

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

不应该包含第 3^rd 行，因为到期日的时间部分早于当前时间的时间部分.

The 3^rd row should not be included because the time part of the due date is earlier than the time part of the current time.

推荐答案

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

You should better store your dates as DATETIME. As you have these values, it already shows your database fields are not of the date type, as inserting such strings into date fields would not produce an error.

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

If this is not possible to change, then you can use the STR_TO_DATE function:

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

但这确实是一个劣质的解决方案.

But that is really an inferior solution.

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

Still, your expectation to only get one row seems strange. Certainly the third row meets the requirement, since the start/due dates are before/after the date you are checking, irrespective of the time parts.

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

In comments you are saying you want to apply a different logic. I'm guessing you want to compare separately the date and time components, and both to comply to the between condition. This really should be explained in the question, as currently it is not specified.

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

In that case, you can use DATE_FORMAT to extract only the time part, and repeat the condition with that:

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 行.

This query will exclude the 3^rd row from the result.

这篇关于AM/PM 不能在 sql 之间使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！