求MySQL查询语句：计算指定时间范围的总使用时长

Got it, let's break down how to compute the total usage time between your paired start and stop entries in MySQL.

Assumptions First #

I'll assume your table is named usage_logs with these columns:

• id: The sequential integer from your sample data (1, 2, 3...)
• action: A VARCHAR field holding values like start, stop, or foo
• event_time: DATETIME column storing the timestamp of each entry

The Query #

Here's the SQL that will pair each valid start with its corresponding stop and sum up the total duration:

SELECT 
    -- Total duration in seconds
    SUM(TIMESTAMPDIFF(SECOND, start_time, stop_time)) AS total_seconds,
    -- Human-readable time format (days, hours, mins)
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, start_time, stop_time))) AS total_duration
FROM (
    -- Subquery to pair each start with its matching stop
    SELECT 
        s.event_time AS start_time,
        (
            SELECT MIN(st.event_time)
            FROM usage_logs st
            WHERE st.action = 'stop'
              AND st.event_time > s.event_time
              -- Ensure no other start comes between this start and stop
              AND NOT EXISTS (
                  SELECT 1
                  FROM usage_logs s2
                  WHERE s2.action = 'start'
                    AND s2.event_time BETWEEN s.event_time AND st.event_time
              )
        ) AS stop_time
    FROM usage_logs s
    WHERE s.action = 'start'
      -- Only include starts within our target range
      AND s.event_time >= '2018-01-13 10:11:00'
) AS valid_pairs
-- Exclude any stops that go beyond our target end time
WHERE stop_time <= '2018-01-18 15:20:00';

How This Works #

1. Inner Subquery (valid_pairs):

   • Grabs all start entries that happen on or after your target start time.
   • For each start, it finds the earliest stop entry that comes after it, with no other start in between (this ensures we don't mix up pairs).

2. Outer Query:

   • Filters out any pairs where the stop time is later than your target end time.
   • Calculates the total duration in two formats: raw seconds (great for calculations) and a human-readable time string (like 2 days 22 hours 28 minutes).

Example Output #

For your sample data, this query will return the sum of:

• Time between 2018-01-13 10:11:00 and 2018-01-14 13:10:00
• Time between 2018-01-16 15:51:00 and 2018-01-18 15:20:00

You can adjust the TIMESTAMPDIFF unit (e.g., HOUR instead of SECOND) if you want duration in a different metric.

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