TIME_TO_SEC is not working Correctly in mysql

Question

" select
SEC_TO_TIME(SUM(TIME_TO_SEC(m.totalTime))) as totalDuration from m inner join pd on pd.candidateId = m.candidateId where m.s_id is not null and pd.s = 1"

OUTPUT :totalDuration: 838:59:59

"select 
       (SUM(TIME_TO_SEC(m.totalTime))/3600)  as totalDuration 
from  m 
inner join pd on pd.candidateId = m.candidateId 
where m.s_Id is not null 
  and pd.s = 1"

Output:totalDuration:1207.7658

My question is why TIME_TO_SEC Function not returning desire output like in first query duration is 838:59:59 and in sec query by dividing 3600 it is shown different result 1207.7658(hr)

What is the desired output? If you can confirm that there is a bug in MySQL, please use their bug tracker — Nico Haase, Mar 04 '22 at 07:28

Narayan Pandey · Answer 1 · 2022-03-09T14:06:53.593

0

Range of SEC_TO_TIME() is -838:59:59 to 838:59:59. So, when we are converting seconds to time if time is greater than 838:59:59 it will always return 838:59:59 value in my case that is same issue . so I have converted sec to hour by divided by 3600.

edited Mar 09 '22 at 14:06

answered Mar 04 '22 at 07:43

Narayan Pandey

21
3

Please remove the parts of this answer that are not relevant to the question. It seems that some irrelevant content was copy-pasted from another source. – TFuto Mar 04 '22 at 09:03

score 0 · Answer 2 · answered Mar 04 '22 at 08:04

SEC_TO_TIME() returns '838:59:59' if an argument is 3020399 or greater.

In your case the value of SUM(TIME_TO_SEC(m.totalTime)) expression seems to be 4347957. It is greater, so the function returns maximal value for TIME datatype.

But when you divide w/o convertion to TIME then the original value is used for division.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=43166d9756e68c60c9abdbf43136b340

TIME_TO_SEC is not working Correctly in mysql

2 Answers2