mysql日期交集_在mysql数据库中查找行和时间戳之间的交集

I have a table, where events of certain objects are listed.

There are two events: "movement" and "load". They can start and end, and these events are listed with timestamps when they happened.

Now i want to calculate the following three numbers:

sum of time, when movement took place (value in the fiddle: 700)

sum of time, when load took place (value in the fiddle: 630)

sum of time, when movement and load took place (value in the fiddle: 611)

I have created a sqlfiddle for the problem here:

解决方案

The first two columns compute the differences between corresponding end/start events.

(Summation is commutative, so we don't need to actually match corresponding events.)

The third colum searches for movement start events for which the preceding load event is a load start event, and for movement end events for which the following load event is a 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;