Warm tip: This article is reproduced from stackoverflow.com, please click
php sql time duration milliseconds

How to convert average time returened from sql query to minutes

发布于 2020-03-29 12:48:12

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

Questioner
Laraib Hassan
Viewed
49
分享
Martin 2020-01-31 17:54

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"
Laraib Hassan 2020-01-31 18:22:56

Thanks a lot , worked perfectly fine as i needed , bundle of thanks

Related issues

1
Update sql database by clicking checkbox without submit button using ajax
2
How to attach when enter a text in laravel?
3
Directus calculated field / column
4
How to count messages for a user with php and mysql
5
How to Remove Array Element and Then Re-Index Array?
6
laravel and multi-sessions from the same browser
7
Calculate working days between to dates
8
How to include a PHP variable inside a MySQL statement
9
is it possible to create SQL table and insert data at the same time?
10
What does "zend_mm_heap corrupted" mean