CODEWITHSUNDEEP
mysqltimesec
Narayan Pandey
Narayan Pandey

Reputation: 31

TIME_TO_SEC is not working Correctly in mysql

" 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)

Upvotes: 0

Views: 548

Answers (2)

Narayan Pandey
Narayan Pandey

Reputation: 31

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.

Upvotes: 0

Akina
Akina

Reputation: 42834

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

Upvotes: 1

Related Questions