0

I am wondering if anyone could help me with a current problem i am having.

I have a database table which has the following columns

enter image description here

I have various different Areas which can have the same product and what i am looking to do is to sum the difference for all the different products in their respective areas. for example in the table above i would get the below results ( product 1 in area 1 total is 3 minutes etc)

Area    Product     Time

Retail  Product 1   00:03:00'

Packing Product 2   00:02:00'

The start and stop columns have the DATETIME datatype

I have tried sum(timediff(stop,start)) and group it by product but this does not appear to work.

Consequently I also want to sum the difference based on the area bit no matter what I put in i always get 181

I would appreciate any help on this

Engodel
  • 64
  • 2
  • 9
  • I'm a little confused with your sample data: You don't really have 2 columns called `start`, do you? Also, for the first 2 rows, the 1st column happens before the 2nd column; for the last 2 rows, the 1st column happens **after** the 2nd column. – Zack Aug 13 '18 at 19:06
  • Dont know how i did that? but you are right one column is start and the other is stop and the stop column will always happen before the start column – Engodel Aug 13 '18 at 19:24
  • Do you mean that the `start` column will always happen before the `stop` column? – Zack Aug 13 '18 at 19:26
  • i'm having a nightmare but yes you are right. – Engodel Aug 13 '18 at 19:34
  • BTW I tried following this thread but to no avail https://stackoverflow.com/questions/4102480/mysql-how-to-sum-a-timediff-on-a-group – Engodel Aug 13 '18 at 19:37

1 Answers1

0

All you need to do is group by your area and product, and sum the times for each. The answer here should help. For your query, you'll just need something like this:

select
    area
    ,product
    ,SEC_TO_TIME(SUM(TIME_TO_SEC(`stop`) - TIME_TO_SEC(`start`))) as timediff
from products_in_area
group by area, product

If you're not tied to showing the end result as a TIME value (e.g. you can do the formatting in your calling application), your query would look a bit cleaner:

select
    area
    ,product
    ,SUM(TIMESTAMPDIFF(MINUTE, `start`, `stop`)) Minutes
from products_in_area
group by area, product
Zack
  • 2,220
  • 1
  • 8
  • 12