1
SELECT TIME_FORMAT(Min(Ontime), '%h:%i %p') AS Ontime, TIME_FORMAT(Max(Offtime), '%h:%i %p') AS Offtime, name,dates, TIME_FORMAT( SEC_TO_TIME( SUM( TIME_TO_SEC(Offtime) - TIME_TO_SEC(Ontime))), "%H:%i") AS `totalHour` FROM netdoc where name='Michael' GROUP BY dates,name

The results that are displaying

 Ontime  | Offtime |totalHours|
 10:06 AM  06:23 PM    08:16

how can i do it like this?

Ontime    | Offtime  |totalHours|
 10:06 AM   06:23 PM   8 hours 16 minutes
Michael
  • 65
  • 6
  • just add the strings into final `TIME_FORMAT` like: `TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(Offtime) - TIME_TO_SEC(Ontime))), "%H hours %i minutes")` – mitkosoft Jan 24 '20 at 07:45

1 Answers1

1

use TIME_FORMAT and below code will help you.

TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(Offtime) - TIME_TO_SEC(Ontime))), "%H hours %i minutes")
Umar Abdullah
  • 1,282
  • 1
  • 19
  • 37