I have a group of machines. They run and stop occasionally. These run times are recorded into a postgres table automatically with timestamps
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
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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX table_idx_starttime ON "table" ("starttime"); CREATE INDEX table_idx_machine ON "table" ("machine");
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