mysql : downtime from date range hourly/daily

187 Views Asked by At

I would like to track machine downtime/production output on hourly basis. Assuming below are the data from 2 tables. I am using unix timestamp but for this example i used date/time for easy refernce

Table 1 - Output
mc    date/time         output       
1    2015-01-24 00:00   10
1    2015-01-24 01:30   5
1    2015-01-24 04:00   5
1    2015-01-24 04:30   10

Table 2 - Downtime        
mc   machine stop        machine start
1    2015-01-24 02:15    2015-01-24 03:55




Output I need

mc   date     hr      output         downtime
1    24 Jan   0       10             0
1    24 Jan   1       5              0
1    24 Jan   2       0              45min ( since machine stopped at 02:25 )
1    24 Jan   3       0              55min ( since machine up at 03:55 )

....so on

Edit : It will be a 24 hours rolling report as such the hour and date will be dynamic

Appreciate your help on this, I hope the solution will apply to monthly query as well

Add --- This is the best I came out with so far, sqlfiddle is below

SELECT lt.date, lt.mc, lt.output, rt.dt FROM 
     (SELECT mc, DATE_FORMAT(FROM_UNIXTIME(timestamp),'%Y%m%d %H:00') as date, 
       sum(output) as output   
       FROM production 
       GROUP BY 
      DATE_FORMAT(FROM_UNIXTIME(timestamp),'%Y%m%d %H:00')
     ) lt
LEFT JOIN (
      SELECT DATE_FORMAT(FROM_UNIXTIME(machine_stop),'%Y%m%d %H:00') as date,
             machine_stop,
             sum(machine_start-machine_stop) as dt, mc  
      FROM downtime 
      GROUP BY 
           DATE_FORMAT(FROM_UNIXTIME(machine_stop),'%Y%m%d %H:00') 
       ) AS rt   
ON   lt.date= rt.date  
UNION 
SELECT rt.date, rt.mc, lt.output, rt.dt FROM 
     (SELECT mc, DATE_FORMAT(FROM_UNIXTIME(timestamp),'%Y%m%d %H:00') as date, 
       sum(output) as output   
       FROM production 
       GROUP BY 
      DATE_FORMAT(FROM_UNIXTIME(timestamp),'%Y%m%d %H:00')
     ) lt
RIGHT JOIN (
      SELECT DATE_FORMAT(FROM_UNIXTIME(machine_stop),'%Y%m%d %H:00') as date,
             machine_stop,
             sum(machine_start-machine_stop) as dt, mc  
      FROM downtime 
      GROUP BY 
           DATE_FORMAT(FROM_UNIXTIME(machine_stop),'%Y%m%d %H:00') 
       ) AS rt   
ON   lt.date= rt.date 
ORDER BY date asc
0

There are 0 best solutions below