Shawn Shawn - 7 months ago 16
SQL Question

MySQL select 0 if difference is less than 0, otherwise select difference

I want to get the difference between StartDate and EndDate, but if the difference is less than 0, I want the value 0 (EndDate could be before StartDate). Is there a way to do this in a SELECT without repeating the ROUND() expression?

SELECT StartDate, EndDate,
IF(ROUND(TIMESTAMPDIFF(SECOND, StartDate, EndDate)/86400, 1) < 0, 0, ROUND(TIMESTAMPDIFF(SECOND, StartDate, EndDate)/86400, 1)) AS Duration
FROM table
WHERE StartDate <= '2016-06-02' AND EndDate >= '2016-06-01';

Answer

Just use GREATEST() to get the larger of 0 and the difference.

SELECT StartDate, EndDate, 
GREATEST(ROUND(TIMESTAMPDIFF(SECOND, StartDate, EndDate)/86400, 1), 0) AS Duration
FROM table
WHERE StartDate <= '2016-06-02' AND EndDate >= '2016-06-01';