how to the more than 1000:00:00 time sum using mysql

Question

i want to get the total number of ot hours in given month it may be more 1000 hours .i already have time sum query but it limited 839:59:59 .how do ignore or anther way to do this task.(ot_hours store in VARCHAR data type)

try query

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `ot_hour` ) ) ) AS timeSum 
                 FROM groupotrequest
                 INNER JOIN department 
                 ON department.dept_Id = groupotrequest.dept_Id
                 WHERE groupotrequest.dept_Id = $dept_Id AND month LIKE '$passmonth%' AND overtime_status=6

sample data

id  month   ot_hour dept_Id overtime_status overtime_type   date    
1   2019-10 2535:00:00  28  6   main_ot 2019-11-21 07:26:00 
2   2019-11 2535:00:00  28  6   main_ot 2019-11-21 07:27:00 
3   2019-10 20:00       28  6   sub_ot  2019-11-21 07:28:00 
4   2019-11 20:00       28  6   sub_ot  2019-11-21 07:30:00

i want to get 2019-11 total ot hours as 2555:00:00

  function GetApprovedOt($connect,$dept_Id,$passmonth)
  {
      $query01 =$connect->prepare('SELECT SUM( TIME_TO_SEC( `ot_hour` )) 
                 FROM groupotrequest
                 INNER JOIN department 
                 ON department.dept_Id = groupotrequest.dept_Id
                 WHERE groupotrequest.dept_Id = :dept_Id AND month LIKE :passmonth% AND overtime_status=6');
      
       $query01->execute([
          ':passmonth'  => $passmonth,
          ':dept_Id'    => $dept_Id
          ]);
               
       list ($totalMins, $remngSecs) = gmp_div_qr($query01->fetchColumn(), 60);
       list ($totalHour, $remngMins) = gmp_div_qr($totalMins, 60);

       echo "Worked a total of $totalHour:$remngMins:$remngSecs.";
  }

i try this also .it also does not working

Does this answer your question? [Surpassing MySQL's TIME value limit of 838:59:59](https://stackoverflow.com/questions/13733148/surpassing-mysqls-time-value-limit-of-8385959) — ProgrammingLlama, Nov 21 '19 at 07:02
[Related question](https://stackoverflow.com/questions/39259910/why-is-mysqls-maximum-time-limit-8385959/39261014) — ProgrammingLlama, Nov 21 '19 at 07:03
Does `SUM` without `SEC_TO_TIME` presumably give the correct value in seconds? Maybe you could perform the conversion to a time string in whatever language you're receiving the results in. (Sorry, I'm not super familiar with the particulars of MySQL). — ProgrammingLlama, Nov 21 '19 at 07:11
And [this answer](https://stackoverflow.com/a/13733724/3181933) doesn't help you? — ProgrammingLlama, Nov 21 '19 at 07:16
i try this code but is not work to me.sometime it may be my mistake — Nipun Sachinda, Nov 21 '19 at 07:21
Note: `:x%` is not a placeholder value. You need to add `%` to the value you're binding, like `':passmonth' => "$passmonth%",` — tadman, Aug 11 '20 at 21:45

Salman A · Accepted Answer · 2019-11-21T09:13:14.467

2

Assuming you have values such as 2535:00:00 in your data you cannot use the time datatype or functions such as time_to_sec.

One workaround is to convert the string 2535:30:00 to minutes as (2535 * 60 + 30 = 152130), perform sum, then convert the sum back to hours and minutes (152130 = 2535 hour, 30 minutes):

SELECT SUM(
           SUBSTRING_INDEX(ot_hour, ':', 1) * 60 +
           SUBSTRING_INDEX(SUBSTRING_INDEX(ot_hour, ':', 2), ':', -1)
       ) div 60 AS sum_hh,
       SUM(
           SUBSTRING_INDEX(ot_hour, ':', 1) * 60 +
           SUBSTRING_INDEX(SUBSTRING_INDEX(ot_hour, ':', 2), ':', -1)
       ) mod 60 AS sum_mm
FROM t

edited Nov 21 '19 at 09:13

answered Nov 21 '19 at 07:37

Salman A

262,204
82
430
521

how could i apply to the my case? – Nipun Sachinda Nov 21 '19 at 07:48
it work in some case but one ot_hour more than 839 it not work it show as 839 how do i fix it – Nipun Sachinda Nov 21 '19 at 08:32
1

See https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ab568532a008378f4a65f7c22a70f980 ... I have assumed 20:00 means 20 hours, 0 minutes. – Salman A Nov 21 '19 at 08:56

how to the more than 1000:00:00 time sum using mysql

1 Answers1