I have a table which contain fail time and the corresponding start time of several machines to track their downtime.
machine_status(
id integer auto_increment,
machine_no integer,
fail_time datetime,
start_time datetime)
I am able to get the total downtime of each machine with
SELECT machine_no, SUM(TIMESTAMPDIFF(SECOND, fail_time, start_time)) duration
from machine_status
GROUP BY machine_no
But, if a machine is down at the moment of executing the query start_time
is NULL. In this case I want to substitute current timestamp to start_time
and then get the time difference.
Basically how can I do something like this?
SELECT machine_no, SUM(TIMESTAMPDIFF(SECOND, fail_time,
IF start_time IS NULL THEN NOW() ELSE start_time)) duration
from machine_status
GROUP BY machine_no
Aucun commentaire:
Enregistrer un commentaire