Shanen Leen Shanen Leen - 5 months ago 21
MySQL Question

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

Answer

This should do the job

SELECT
  ms.machine_no AS machine_no,
  SUM(
    TIMESTAMPDIFF(SECOND, ms.fail_time, IFNULL(ms.start_time, NOW()) )
  ) AS duration
FROM machine_status AS ms
GROUP BY ms.machine_no
Comments