MySQL对时间一样的进行求和,MySQL：对日期时间求和，但不计算重叠时间两次

待我称王封你为后i 2022-08-30 11:17 226阅读 0赞

![Image 1][]

I need to count the total amount of time spent based on a table like:

id | start\_time | end\_time |

where periods can overlap. I need to count ovelpapping periods only once.

E.g. if I have periods like these:

\*----A----\* \*------C-----\* \*----------D----------\*

\*-----B-----\* \*---E---\*

the sum will be: (A.end-A.start) + (C.end - B.start) + (D.end - D.start)

I'm a bit confused with the approach I should use to write this query and will be grateful for help.

解决方案

Ok, I seriously insist you test this in all manners before using it in production.

Especially test what happens if there are MULTIPLE overlaps for 1 time span.

What this query does is calculate the duration of each time span, and how much overlap exists with other time spans that have a higher id.

select

t1.id,

t1.start\_time,

t1.end\_time,

t1.end\_time - t1.start\_time as duration,

sum(

if(t2.start\_time < t1.start\_time and t2.end\_time > t1.end\_time , t1.end\_time - t1.start\_time, 0) -- t2 completely around t1

\+ if(t2.start\_time >= t1.start\_time and t2.end\_time <= t1.end\_time , t2.end\_time - t2.start\_time, 0) -- t2 completely within t1

\+ if(t2.start\_time < t1.start\_time and t2.end\_time > t1.start\_time and t2.end\_time < t1.end\_time , t2.end\_time - t1.start\_time, 0) -- t2 starts before t1 starts and overlaps partially

\+ if(t2.start\_time < t1.end\_time and t2.end\_time > t1.end\_time and t2.start\_time > t1.start\_time, t1.end\_time - t2.start\_time, 0) -- t2 starts before t1 ends and overlaps partially

) as overlap

from

times t1

left join times t2 on

t2.id > t1.id -- t2.id is greater than t1.id

and (

(t2.start\_time < t1.start\_time and t2.end\_time > t1.end\_time ) -- t2 completely around t1

or (t2.start\_time >= t1.start\_time and t2.end\_time <= t1.end\_time ) -- t2 completely within t1

or (t2.start\_time < t1.start\_time and t2.end\_time > t1.start\_time) -- t2 starts before t1 starts and overlaps

or (t2.start\_time < t1.end\_time and t2.end\_time > t1.end\_time ) -- t2 starts before t1 ends and overlaps

)

group by

t1.id

So what you want to have in the end is this:

select

sum(t.duration) - sum(t.overlap) as filtered\_duration

from

(

OTHER QUERY HERE

) as t

So in the end you have this query:

select

sum(t.duration) - sum(t.overlap) as filtered\_duration

from

(

select

t1.id,

t1.start\_time,

t1.end\_time,

t1.end\_time - t1.start\_time as duration,

sum(

if(t2.start\_time < t1.start\_time and t2.end\_time > t1.end\_time , t1.end\_time - t1.start\_time, 0) -- t2 completely around t1

\+ if(t2.start\_time >= t1.start\_time and t2.end\_time <= t1.end\_time , t2.end\_time - t2.start\_time, 0) -- t2 completely within t1

\+ if(t2.start\_time < t1.start\_time and t2.end\_time > t1.start\_time and t2.end\_time < t1.end\_time , t2.end\_time - t1.start\_time, 0) -- t2 starts before t1 starts and overlaps partially

\+ if(t2.start\_time < t1.end\_time and t2.end\_time > t1.end\_time and t2.start\_time > t1.start\_time, t1.end\_time - t2.start\_time, 0) -- t2 starts before t1 ends and overlaps partially

) as overlap

from

times t1

left join times t2 on

t2.id > t1.id -- t2.id is greater than t1.id

and (

(t2.start\_time < t1.start\_time and t2.end\_time > t1.end\_time ) -- t2 completely around t1

or (t2.start\_time >= t1.start\_time and t2.end\_time <= t1.end\_time ) -- t2 completely within t1

or (t2.start\_time < t1.start\_time and t2.end\_time > t1.start\_time) -- t2 starts before t1 starts and overlaps

or (t2.start\_time < t1.end\_time and t2.end\_time > t1.end\_time ) -- t2 starts before t1 ends and overlaps

)

group by