TIME_TO_SEC is not working Correctly in mysql-CodePudding

select pd.specialization , 
       SEC_TO_TIME(SUM(TIME_TO_SEC(m.totalTime))) as totalDuration 
from mergedVideos m 
inner join professionaldetails pd on pd.candidateId = m.candidateId 
where m.interviewSessionId is not null 
  and pd.specialization = 1

OUTPUT

specialization :1       totalDuration: 838:59:59

select pd.specialization , 
       (SUM(TIME_TO_SEC(m.totalTime))/3600)  as totalDuration 
from mergedVideos m 
inner join professionaldetails pd on pd.candidateId = m.candidateId 
where m.interviewSessionId is not null 
  and pd.specialization = 1

specialization:1   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)

CodePudding user response：

Limitations Of The Time Datatype One thing you should know while working with the SEC_TO_TIME() function is that the time datatype has a limitation. The time data type is limited to a range from -838:59:59 to 838:59:59. So, if you pass a huge seconds value, enough to cross this range then you will get a warning. Let us see this behaviour using the two below queries.

CodePudding user response：

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