CODEWITHSUNDEEP
mysqlsql
SaidbakR
SaidbakR

Reputation: 13544

Implement IF in MySQL SUM Timestamps difference

I have the following query that sum time difference I want it to set negative time difference to zero or neglect it.

SELECT 
       SUM(TIMESTAMPDIFF(MINUTE, GREATEST('2017-10-16 16:00:00',created_at),LEAST('2017-10-17 00:00:00',IFNULL(closed,NOW()))

                         )) AS totalWork, created_at, closed
         FROM cavity_actions WHERE  cavity_id = 52 AND action_id = 10 AND job_id = 42 GROUP BY created_at, closed

This query returns:

totalWork    created_at            closed
-12588       2017-10-07 21:12:48   2017-10-07 22:11:08
480          2017-10-07 22:17:45   NULL

I need to neglect the negative value or set it to zero.

I have tried to use the totalWork alias in the WHERE clause but Unknown column error is generated.

Is there any way that allows to set each TIMESTAMPDIFF() output in a variable then using it in IF clause?

I have tried :

SELECT 
        SUM(@td := TIMESTAMPDIFF(MINUTE, GREATEST('2017-10-16 16:00:00',created_at),LEAST('2017-10-17 00:00:00',IFNULL(closed,NOW()))

                         ) IF(td > 0,td,0)) AS totalWork, created_at, closed
         FROM cavity_actions WHERE cavity_id = 52 AND action_id = 10 AND job_id = 42 GROUP BY created_at, closed

But it returns error.

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

No need for IF() (or CASE, which is the ANSI standard). You can use GREATEST():

SELECT SUM(GREATEST(TIMESTAMPDIFF(MINUTE,
                                  GREATEST('2017-10-16 16:00:00', created_at),
                                  LEAST('2017-10-17 00:00:00', COALESCE(closed, NOW()))

                                 ), 0
                   )
          ) AS totalWork,
       created_at, closed
FROM cavity_actions
WHERE cavity_id = 52 AND action_id = 10 AND job_id = 42
GROUP BY created_at, closed

Upvotes: 2

Related Questions