如何在MySQL中查询autoresetsettings表指定时间区间的数据

Let's break down how to fetch rows from your autoresetsettings table where the time range defined by fromDate and todate aligns with your specified intervals.

First, a quick note: I’ll assume fromDate and todate are either TIME or DATETIME type columns. If they’re DATETIME, we’ll use the TIME() function to extract just the time component for comparison.

Scenario 1: Fetch rows where the entire time range fits inside your target intervals #

If you want records where their full [fromDate, todate] window sits entirely within either 04:40 AM–06:15 AM, 04:40 PM–06:15 PM, or 04:40 AM–06:15 PM, here’s the query:

SELECT *
FROM autoresetsettings
WHERE
    -- Time range is within 04:40 AM to 06:15 AM
    (TIME(fromDate) >= '04:40:00' AND TIME(todate) <= '06:15:00')
    OR
    -- Time range is within 04:40 PM to 06:15 PM
    (TIME(fromDate) >= '16:40:00' AND TIME(todate) <= '18:15:00')
    OR
    -- Time range is within 04:40 AM to 06:15 PM
    (TIME(fromDate) >= '04:40:00' AND TIME(todate) <= '18:15:00');

A quick simplification: the third condition already includes the first two, so if you don’t need to distinguish between them, you could just use the third clause alone. But if you want explicit checks for each interval, the above works as-is.

Scenario 2: Fetch rows where the time range overlaps with your target intervals #

If you want records where their [fromDate, todate] window overlaps with any of your target intervals (even partially), we use the standard overlap rule: two intervals [a,b] and [c,d] overlap if a < d and c < b.

Applying that to your targets:

SELECT *
FROM autoresetsettings
WHERE
    -- Overlap with 04:40 AM–06:15 AM
    (TIME(fromDate) < '06:15:00' AND '04:40:00' < TIME(todate))
    OR
    -- Overlap with 04:40 PM–06:15 PM
    (TIME(fromDate) < '18:15:00' AND '16:40:00' < TIME(todate))
    OR
    -- Overlap with 04:40 AM–06:15 PM
    (TIME(fromDate) < '18:15:00' AND '04:40:00' < TIME(todate));

Again, the third condition covers overlaps with the first two, so feel free to simplify if you don’t need separate checks.

Optimizing for TIME type columns #

If fromDate and todate are already TIME type (not DATETIME), you can remove the TIME() wrapper to make the query faster:

-- Simplified query for TIME columns (covers all your example intervals)
SELECT *
FROM autoresetsettings
WHERE fromDate >= '04:40:00' AND todate <= '18:15:00';

Bonus: Handling cross-midnight intervals #

If your table has intervals that cross midnight (e.g., 10 PM to 2 AM), the logic gets a bit more complex—but since your examples don’t mention this, I’ll skip it for now. Just let me know if you need to handle those cases!

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