如何用SQL计算两个日期时间间隔并排除每日固定休息时段

Got it, let's tackle this problem step by step. When you need to compute the time between two datetimes while excluding a daily fixed break window (like 14:30-14:45), you can't just subtract the total break time for all days—you have to account for partial breaks on the start and end dates, plus full breaks on the days in between.

Core Logic #

1. Calculate the total raw duration between the two times.
2. Subtract the break time for:
   • Full days: Every complete day between the start and end date.
   • Start day: Any overlap between the start time and the daily break window.
   • End day: Any overlap between the end time and the daily break window.

Example MySQL Query #

Let's use your sample times (t1='02/12/2018 14:18:29', t2='02/14/2018 14:18:29') and break window (14:30-14:45) to build the query:

SELECT
  -- Convert the final effective seconds to a human-readable time format
  SEC_TO_TIME(
    -- Total raw seconds between t1 and t2
    TIMESTAMPDIFF(SECOND, t1, t2)
    -- Subtract total break time
    - (
      -- Break time for full days between start and end
      IF(DATEDIFF(t2, t1) > 0, (DATEDIFF(t2, t1) - 1) * TIMESTAMPDIFF(SECOND, '14:30:00', '14:45:00'), 0)
      -- Break time on the start day (partial if needed)
      + CASE
          WHEN t1 >= CONCAT(DATE(t1), ' 14:45:00') THEN 0 -- Start time is after break ends
          WHEN t2 <= CONCAT(DATE(t1), ' 14:30:00') THEN 0 -- Entire period is before break starts
          ELSE TIMESTAMPDIFF(SECOND, GREATEST(t1, CONCAT(DATE(t1), ' 14:30:00')), LEAST(t2, CONCAT(DATE(t1), ' 14:45:00')))
        END
      -- Break time on the end day (partial if needed)
      + CASE
          WHEN t2 <= CONCAT(DATE(t2), ' 14:30:00') THEN 0 -- End time is before break starts
          WHEN t1 >= CONCAT(DATE(t2), ' 14:45:00') THEN 0 -- Entire period is after break ends
          ELSE TIMESTAMPDIFF(SECOND, GREATEST(t1, CONCAT(DATE(t2), ' 14:30:00')), LEAST(t2, CONCAT(DATE(t2), ' 14:45:00')))
        END
    )
  ) AS effective_duration
FROM (
  -- Replace these with your actual column names or values
  SELECT STR_TO_DATE('02/12/2018 14:18:29', '%m/%d/%Y %H:%i:%s') AS t1,
         STR_TO_DATE('02/14/2018 14:18:29', '%m/%d/%Y %H:%i:%s') AS t2
) AS time_range;

Breakdown of the Query #

• Total Raw Duration: TIMESTAMPDIFF(SECOND, t1, t2) gives the total seconds between the two times.
• Full Days Break: (DATEDIFF(t2, t1) - 1) counts the complete days between start and end (e.g., 12th to 14th has 1 full day: 13th). Multiply by the break duration (900 seconds = 15 minutes).
• Start Day Break: Uses GREATEST() and LEAST() to find the overlap between the start time and the break window. If there's no overlap, returns 0.
• End Day Break: Same logic as the start day, but checks the end time's overlap with the break window.

Handling Edge Cases #

• Same Day: If t1 and t2 are on the same day, the full days break calculation returns 0, and only the start/end day logic runs (which is the same as checking the single day's overlap).
• Start Time Mid-Break: If t1 starts during the break, the query only subtracts the time from t1 to the end of the break.
• End Time Mid-Break: If t2 ends during the break, only the time from the start of the break to t2 is subtracted.

For Other Databases (e.g., PostgreSQL) #

The logic stays the same—only the functions change:

• Use EXTRACT(EPOCH FROM t2 - t1) for total seconds.
• Use DATE_PART('day', t2 - t1) for day differences.
• Combine date and time with DATE(t1) + TIME '14:30:00'.

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