CODEWITHSUNDEEP
mysql
Ullas Sharma
Ullas Sharma

Reputation: 450

Sum of timeStamp in Mysql

I'm trying to get the average of Completed time, so at first, I'm getting the completed time of individual processOrders.

below Query will get the Completed time for Individual processOrders. 

select processorder_prod_number,ifnull(time(round( timediff(max(completed_time), 
min(start_time)))) ,'00:00:00') as `completedtime`
from processorder_prod where week(start_time)=week(curdate())
group by processorder_prod_number;

Output is like this

+--------------------+----------------+
| processorder_number| completedtime  | 
+--------------------+----------------+
|   1002135897       |  01:42:15      |
|   1002135898       |  01:39:43      |
|   1002135900       |  05:31:52      |
|   1002135901       |  02:31:52      |
+--------------------+----------------+

when I tried to get the total time, i.e sum(completedtime), it's getting the wrong output.

select count(distinct processorder_prod_number),time(round(sum(completedtime))) as totaltime from
(select processorder_prod_number,ifnull(time(round( timediff(max(completed_time), 
min(start_time)))) ,'00:00:00') as `completedtime`
from processorder_prod where week(start_time)=week(curdate())
group by processorder_prod_number) as t;

Output from above query

+---------------------------+----------------+
| count(processorder_number)| completedtime  | 
+---------------------------+----------------+
|   4                       |   01:42:15     |
+---------------------------+----------------+

please help me where I'm doing wrong.Any suggestions and corrections welcomed, thanks in advance.

Upvotes: 2

Views: 257

Answers (1)

Aishwarya
Aishwarya

Reputation: 433

Here, You will get Sum of Completed Time.

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) AS `completedtime` FROM dual;

Updated Answer

 SELECT COUNT(DISTINCT processorder_prod_number), TIME(ROUND(SUM(completedtime))) AS  totaltime FROM (SELECT processorder_prod_number, IFNULL(SEC_TO_TIME( SUM( TIME_TO_SEC( `completed_time` ) ) ),'00:00:00') AS  `completedtime`  FROM processorder_prod WHERE WEEK(start_time)=WEEK(CURDATE())  GROUP BY processorder_prod_number) AS t;

Upvotes: 1

Related Questions