1

I have a table with the following four DATETIME columns and the last fifth column INTEGER: time_in1, time_out1, time_in2, time_out2, total_seconds.

The function for the "total_seconds" column is 

((TIMESTAMPDIFF(SECOND,time_in1,time_out1))+(TIMESTAMPDIFF(SECOND,time_in2, time_out2)))

Is there a way to make the "total_seconds" show the total even if only "time_in1" and "time_out1" are filled with the right data but the other two columns are NULL?

I know that I could create more columns to separately calculate total seconds for each interval and then SUM all of them in the total column. But it would be nice to make it work like above.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Endriu Andrei
  • 53
  • 1
  • 5

1 Answers1

1

The null DATETIME columns cause the TIMESTAMPDIFF() function to return NULL. So you need to conditionalize those values with COALESCE(value, default).

  COALESCE(TIMESTAMPDIFF(SECOND,time_in1,time_out1),0) 
+ COALESCE(TIMESTAMPDIFF(SECOND,time_in2, time_out2),0)

This works if you want to use zero seconds for the cases where either, or both, DATETIME values are null.

O. Jones
  • 103,626
  • 17
  • 118
  • 172