CODEWITHSUNDEEP
mysql
Sameek Mishra
Sameek Mishra

Reputation: 9384

Get correct time difference between two times

I have requirement to get the time difference between two times(24-hours format).Sum all find difference and minus to allocated weekly hours.

For finding difference between two time(start_time and end_time). I have used mysql query:

 SELECT es.empid,es.shiftid, es.start_time,es.end_time,TIMEDIFF(es.end_time, es.start_time) FROM 
`empschedule` AS es INNER JOIN `employee` AS e ON  es.`empId`=e.`empID`

Query Result:

enter image description here

For first two records i found correct difference but for last record i got wrong difference.please help me how i get correct difference.

Upvotes: 0

Views: 126

Answers (2)

Iłya Bursov
Iłya Bursov

Reputation: 24146

I suppose OP looks for:

select
    es.empid,
    es.shiftid,
    es.start_time,
    es.end_time,
    case when (es.end_time > es.start_time) then
        timediff(es.end_time, es.start_time)
    else
        ADDTIME(timediff('24:00:00', es.start_time), es.end_time)
    end
FROM `empschedule` AS es
INNER JOIN `employee` AS e
ON  es.`empId`=e.`empID`

Upvotes: 3

Sadikhasan
Sadikhasan

Reputation: 18600

Try with ABS() function

SELECT es.empid,es.shiftid, es.start_time,es.end_time,ABS(TIMEDIFF(es.end_time, es.start_time)) FROM 
`empschedule` AS es INNER JOIN `employee` AS e ON  es.`empId`=e.`empID`

Upvotes: 0

Related Questions