title: Mysql - average time spent
body: <p>I've got the following table:</p>
  <pre><code>+-----------------+-------------------+----------+
  |container        |finish_time        |product   |
  +-----------------+-------------------+----------+
  |10405402         |2021-03-15 18:57:26|192650    |
  |10405402         |2021-03-15 18:57:16|259619    |
  |10405402         |2021-03-16 13:54:08|1735      |
  |10405402         |2021-03-16 15:46:07|1735      |
  |10405402         |2021-03-16 15:46:07|1735      |
  |10405402         |2021-03-16 15:46:07|1735      |
  |10405402         |2021-03-16 13:54:08|9186      |
  |10405402         |2021-03-16 15:46:07|9186      |
  |10405402         |2021-03-16 15:46:07|9186      |
  |10405402         |2021-03-16 15:46:07|9186      |
  |10405402         |2021-03-16 13:54:08|7609      |
  |10405402         |2021-03-16 15:46:07|7609      |
  |10405402         |2021-03-16 15:46:07|7609      |
  |10405402         |2021-03-16 15:46:07|7609      |
  |10405403         |2021-03-16 18:29:37|1735      |
  |10405403         |2021-03-16 18:29:37|1735      |
  |10405403         |2021-03-16 18:29:37|1735      |
  |10405403         |2021-03-16 18:29:37|9186      |
  |10405403         |2021-03-16 18:29:37|9186      |
  |10405403         |2021-03-16 18:29:37|9186      |
  |10405404         |2021-03-16 18:41:26|7609      |
  |10405404         |2021-03-16 18:41:26|7609      |
  |10405404         |2021-03-16 18:41:26|7609      |
  |10405410         |2021-03-16 18:45:38|1735      |
  |10405410         |2021-03-16 18:45:38|1735      |
  |10405409         |2021-03-16 18:47:07|9186      |
  |10405409         |2021-03-16 18:47:07|9186      |
  |10405406         |2021-03-16 18:48:38|7609      |
  |10405406         |2021-03-16 18:48:38|7609      |
  |10405406         |2021-03-16 18:52:52|1735      |
  |10405406         |2021-03-16 18:52:52|1735      |
  |10405406         |2021-03-16 18:52:52|1735      |
  |10405406         |2021-03-16 18:52:52|1735      |
  |10405406         |2021-03-16 18:52:52|1735      |
  |10405402         |2021-03-16 18:54:49|1735      |
  |10405402         |2021-03-16 18:54:49|1735      |
  |10405402         |2021-03-16 18:54:49|1735      |
  |10405402         |2021-03-16 18:54:49|1735      |
  |10405402         |2021-03-16 18:54:49|1735      |
  +-----------------+-------------------+----------+
  </code></pre>
  <p>I need to extract an average of time spent to empty the container divided by products number so in the final to obtain the average time spent on product. In this situation the main issue is that i can not figure out when the container products end. The start time of a container is the first finish_time and the end of a container is the last finish time. As you can see the issue is also that a container is repeated at some time..
  What I’ve tried is something like:</p>
  <pre><code>SELECT container, tt
  from (SELECT (to_seconds(max(finish_time)) - to_seconds(min(finish_time))) as tt, container
        FROM timing
        group by container
        having tt &lt; 3600) as tt2;
  </code></pre>
  <p>The result should be like
  Seconds_per_product = 34.8571;</p>
  <p>The average time to empty to container - on example: I've got container no. 10405402 , the first registration is 2021-03-15 18:57:26 - so i will take that like the begin time. The lastest registration of this container is 2021-03-15 18:57:16 - so this will be the last action on this container . I need to make the difference between the last and the first action on the container, divided by how many products are in this container (for this example. will be 14) . As a rule, if the difference of time is &gt; 1h for the same container this should be calculated separately as another action .. So for the upper example the result should be 10 seconds for 2 products =&gt; average = 5 seconds / product.
  I'am using Mysql Version 5.7</p>