SQL中使用BETWEEN时AM/PM时间筛选异常问题排查

Hey, let's break down why your original query isn't working and fix it properly.

The BETWEEN operator compares the full datetime timestamp as a single value. For the 3rd record, 2018-01-19 03:56 PM is indeed earlier than 2018-01-22 10:30 AM, so it gets included. But this doesn't match your actual requirement: you want records where both the current date falls within the task's date range, and the current time (AM/PM included) falls within the task's time range.

We need to split the logic into two separate conditions that both must be true:

• The current date is between the date parts of t_started_on and t_due_on
• The current time (with AM/PM) is between the time parts of t_started_on and t_due_on

Here's how to implement this in MySQL (adjust functions for other databases as needed):

SELECT * 
FROM `task`
WHERE 
  -- Check if current date is within the task's date range
  DATE('2018-01-19 03:56 PM') BETWEEN DATE(t_started_on) AND DATE(t_due_on)
  AND
  -- Check if current time (AM/PM included) is within the task's time range
  TIME_FORMAT('2018-01-19 03:56 PM', '%h:%i %p') BETWEEN TIME_FORMAT(t_started_on, '%h:%i %p') AND TIME_FORMAT(t_due_on, '%h:%i %p');

• DATE() extracts just the date component, ensuring we only check if today falls between the task's start and end dates.
• TIME_FORMAT() converts the datetime to a string with AM/PM, letting us compare the time parts independently of the date. This filters out cases like the 3rd record, where the date is valid but the current time is later than the task's due time.

Running this query will return only the 2nd record:

• Record 2: Date (19th) is between 18th-19th, time (3:56 PM) is between 9:15 AM-7:00 PM → valid.
• Record 3: Date (19th) is between 16th-22nd, but time (3:56 PM) is later than 10:30 AM → invalid, excluded.

If you're using a different SQL dialect, swap the date/time functions:

• PostgreSQL: Use TO_CHAR(datetime, 'HH12:MI AM') for time formatting, DATE(datetime) for date extraction.
• SQL Server: Use CONVERT(VARCHAR, datetime, 109) for time with AM/PM, CAST(datetime AS DATE) for date extraction.

内容的提问来源于stack exchange，提问作者Mithun M