query over multiple rows

[Gwoemul]

Hi everyone,

 

I've got a table with times  (columns have mysql type: time )

like this

 

time_fromtime_to

09:00:0012:00:00

08:00:0014:00:00

11:00:0013:00:00

16:00:0018:00:00

19:00:0020:00:00

 

I want to get the total time including overlapping time periods which would be:

 

08-14  =  6 h +

16-18  =  2 h +

19-20  =  1 h

 

= 9 hours total -  (this should be the result of the query)

 

because times can overlap each other in different rows, I can't figure out an easy way to construct a

query that can perform a check across multiple rows and give me the result I want.

 

Any help would be highly appreciated

 

Cheers

 

 

[fenway]

Yes, that's going to be quite difficult in mysql... you should consider doing this in php.

[Gwoemul]

hi fenway

 

thanks for your reply.

yeah I'm doing it in php at the moment but I thought there might be a more elegant way in mysql which I don't know of.

 

only solution I could think of is  joining the table with itself somehow and then ignoring

the rows of where time_from or time_to of table 2 is between time_from and time_to of table 1.

 

but somehow its not working as I wanted it to.

 

cheers

 

[fenway]

It's surprisingly difficult to compare rows to each other... this falls far outside the scope of the "set" that almost all RDBMS are based upon.  You'd need a join for *each* row! That is, it needs to be interative... so unless you're going to use an SP, you're stuck.