如果差异小于0，MySQL选择0，否则选择差异

Question

我想得到StartDate和EndDate之间的差值，但如果差值小于0，我想要值0（EndDate可以在StartDate之前）。有没有一种方法可以在SELECT中做到这一点而不重复ROUND（）表达式？

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 1

只需使用GREATEST()即可获得0和差值中的较大值。

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';