Geek Answers Handbook

Find intersections between rows and timestamps in mysql db

I have a table that lists the events of certain objects.

There are two events: “motion” and “loading”. They can begin and end, and these events are listed with timestamps when they occurred.

Now I want to calculate the following three numbers:

  • the amount of time the movement took place (value in the violin: 700)
  • the amount of time the download took place (value in the script: 630)
  • the sum of the time when the movement and load took place (value in the violin: 611)

I created sqlfiddle for the problem here:

http://sqlfiddle.com/#!2/be512

+6
source share
3 answers

The first two columns compute the differences between the corresponding end / start events. (Summation is commutative, so we do not need to actually match the corresponding events.)

The third color looks for movement start events for which the previous load event is the load start event, and for movement end events for which the next load event is the load end event.

 SELECT (SELECT SUM(timestamp) FROM Table1 WHERE event = 'movement end') - (SELECT SUM(timestamp) FROM Table1 WHERE event = 'movement start') AS all_movement, (SELECT SUM(timestamp) FROM Table1 WHERE event = 'load end') - (SELECT SUM(timestamp) FROM Table1 WHERE event = 'load start') AS all_load, (SELECT SUM(timestamp) FROM Table1 a WHERE event = 'movement end' AND (SELECT event FROM Table1 b WHERE timestamp = (SELECT min(timestamp) FROM Table1 c WHERE c.timestamp >= a.timestamp AND c.event LIKE 'load %') ) = 'load end') - (SELECT SUM(timestamp) FROM Table1 a WHERE event = 'movement start' AND (SELECT event FROM Table1 b WHERE timestamp = (SELECT max(timestamp) FROM Table1 c WHERE c.timestamp <= a.timestamp AND c.event LIKE 'load %') ) = 'load start') AS load_movement;
+1
source

try it

To answer the download, there will be 630 not 690

For the first and second cases

 SELect max(timestamp) - min(timestamp), LEFT(event, LOCATE(' ', event)) FROM table1 group by id, LEFT(event, LOCATE(' ', event));

For the 3rd case

 SELect max(timestamp) - min(timestamp), id FROM table1 group by id;

Fiddle

+2
source

This is a request to get the time between the beginning and the end:

 select sum(a.timestamp-b.timestamp) from Table1 a join Table1 b on a.rowid-b.rowid=1 and a.rowid%2=0 and a.event='movement end';

similar, between end and start:

 select sum(a.timestamp-b.timestamp) from Table1 a join Table1 b on a.rowid-b.rowid=1 and a.rowid%2=1 and a.event='movement start';
+1
source

More articles:


All Articles