dimanche 3 juillet 2016

MySQL how to get the time difference, if second argument is NULL by using TIMESTAMPDIFF?

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