[Solved] SQL solution to overlapping timeframes

EverSQL Database Performance Knowledge Base

SQL solution to overlapping timeframes

Database type:

I have a group of machines. They run and stop occasionally. These run times are recorded into a postgres table automatically with timestamps starttime and endtime.

I need to find the run time per machine in a 6 hour period. This is what I have so far: 

SELECT machine, SUM(EXTRACT(EPOCH FROM (endtime - starttime)))
FROM table
WHERE 
      starttime >= '2016-01-27 12:00:00'
      AND starttime <= '2016-01-27 18:00:00'
GROUP BY machine
ORDER BY machine

So this works, I get the run time in seconds by machine over that time period. BUT it has a flaw - any run times that started before 12:00 do not get counted. And any run times that started in my time frame but don't end until after it have time counted that shouldn't be there.

Is there a solution to ONLY extract the time that is inside the time frame? My initial thought would be to select all rows where:

endtime >= '2016-01-27 12:00:00'

and somehow, in memory, set all the start times at '2016-01-27 12:00:00' where the start time is earlier than that and:

starttime <='2016-01-27 18:00:00'

and, again in memory without updating the database, set all end times to '2016-01-27 18:00:00' where the end time is later than that. and then run the extraction/summation query.

But I'm struggling on how to implement something like this. I have a working solution in Java/Python this data is returned to, but they are iterative and take more time than I'd like. I'd really like to find an SQL solution to this if possible.

Edit: To clarify, I need to count ALL run time that occurs within the time frame - if a run starts before the time frame only the portion of that run that occurs after the time frame should be counted.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX table_idx_starttime ON "table" ("starttime");
CREATE INDEX table_idx_machine ON "table" ("machine");
The optimized query:
SELECT
        table.machine,
        SUM(EXTRACT(EPOCH 
    FROM
        (endtime - starttime))) 
    FROM
        table 
    WHERE
        table.starttime >= '2016-01-27 12:00:00' 
        AND table.starttime <= '2016-01-27 18:00:00' 
    GROUP BY
        table.machine 
    ORDER BY
        table.machine

Related Articles



* original question posted on StackOverflow here.