CODEWITHSUNDEEP
mysql
Hari Om
Hari Om

Reputation: 56

MySql SUM function results similar answer for my Query while data is different

I have lot more data in mysql database, I want to calculate the duration by TIMEDIFF(actual_end_time,actual_start_time) per month. But it is giving me same figure of output which is not possible. My Query is:

SELECT
  TIME_FORMAT(
    SEC_TO_TIME( SUM( TIME_TO_SEC(
      TIMEDIFF( eo.actual_end_time, eo.actual_start_time )))), '%Hh %im %ss') AS timeDiff
  ,MONTH(date_created) AS MONTH 
  FROM event_operations AS eo 
  GROUP BY MONTH(date_created);

Output:

timeDiff        month
150h 26m 42s     NULL
102h 53m 59s      5
838h 59m 59s      6
838h 59m 59s      7
838h 59m 59s      8
838h 59m 59s      9
838h 59m 59s     10
838h 59m 59s     11
838h 59m 59s     12

I am getting this similar from 6th month to 12th month which is not possible. Any help would be appreciable.

Upvotes: 1

Views: 75

Answers (1)

fthiella
fthiella

Reputation: 49049

The problem is the TIME_TO_SEC function, please see the documentation here about the time type.

TIME values may range from '-838:59:59' to '838:59:59'.

As a workaround, I would try with a query like this:

select
  month,
  concat_ws(' ',
    concat((sec DIV 60*60), 'h'),
    concat((sec DIV 60) % 60, 'm'),
    concat(sec % 60, 's')
  )
from (
select
  SUM(
    to_seconds(
      TIMEDIFF(eo.actual_end_time,eo.actual_start_time)
    )
  ) sec,
  month(date_created) as month
from
  event_operations as eo
) s;

Upvotes: 1

Related Questions