StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I only dabble in database, but the problem seemed interesting to me.</p> <p>From what I'm getting, it sounds like you are more interested in how to write the query than how to store the data. Based on your explanation, what you want is a query whose output looks something like this:</p> <pre><code>classroom openStart openEnd day session A101 9:00 10:00 mo spring A101 14:00 16:00 mo spring </code></pre> <p>Assuming schema test, table class. Class holds a startTime and endTime value. Other values on which filtering will be performed are ignored here to focus on just the logic you are interested in. This query was run on MySQL.</p> <pre><code>-- 8 and 18 are added as the boundaries for the day. Select * from (Select * from ((select endTime as a from test.class) union (select 8)) as t1 join ((select startTime as b from test.class) union (select 18 as startTime)) as t2 where a <= b group by a order by a, b) as t3 where a != b; </code></pre> <p>Three subqueries.</p> <p>The first creates a cross-product of all end times (with the start time for the day appended) by all start times (with the end time for the day appended in). What this gives you is a series of rows representing every conceivable open block of time. (arranged as block start/ block end)</p> <p>The second then pares the results of the first subquery by filtering out values where block start happens after block end, then sorts the values by block start, block end. This makes it so that when the values are grouped by block-start, you get a nice list of pairs that make it easy to spot places where classes are continuous.</p> <p>The third query then filters out the values where the times are the same, giving you just the time blocks that are relevant. Hypothetically the third query could use a comparison that ruled out cases where classes were less than an hour apart as well.</p> <p>I'm sure others might have somewhat simpler solutions for you.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload