Skip to content
Advertisement

How to convert average time returened from sql query to minutes [closed]

I am running the following query

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(Response_Time))) as avg_time
FROM    Case_report
where Reporting_Time between "'.$fromdate.'" and "'.$todate.'"
  and Police_Circle="Sadar"

and getting the correct result from sql as ” 00:19:35.6000 ”

I need to get result in total minutes Like 20

Advertisement

Answer

In this part of your SQL, you convert the total seconds into a time, which is why you get your output as time:

SEC_TO_TIME(AVG(TIME_TO_SEC(Response_Time)))

Instead of converting the result back to a time, just divide the seconds by 60 and round the number to get the total minutes:

ROUND(AVG(TIME_TO_SEC(Response_Time)) / 60)

In your original SQL statement:

SELECT  ROUND(AVG(TIME_TO_SEC(Response_Time)) / 60) AS avg_time
  FROM  Case_report
  where Reporting_Time between "'.$fromdate.'" and "'.$todate.'"
        and Police_Circle="Sadar"
Advertisement