SQL - Finding open spaces in a schedule

Question

I am working with a SQLite database and I have three tables describing buildings,rooms and scheduled events.

The tables look like this:

Buildings(ID,Name)
Rooms(ID,BuildingID,Number)
Events(ID,BuildingID,RoomID,Days,s_time,e_time)

So every event is associated with a building and a room. The column Days contains an integer which is a product of prime numbers corresponding to days of the week ( A value of 21 means the event occurs on Tuesday = 3 and Thursday = 7).

I am hoping to find a way to generate a report of rooms in a specific building that will be open in the next few hours, along with how long they will be open for.

Here is what I have so far:

SELECT Rooms.Number 
FROM Rooms
INNER JOIN Buildings on ( Rooms.BuildingID = Buildings.ID )
WHERE 
  Buildings.Name = "BuildingName"

EXCEPT

SELECT Events.RoomID
FROM Events
INNER JOIN Buildings on ( Events.BuildingID = Buildings.ID )
WHERE
  Buildings.Name = "BuildingName" AND
  Events.days & 11 = 0 AND
  time("now", "localtime" BETWEEN events.s_time AND events.e_time;

Here I find all rooms for a specific building and then I remove rooms which currently have an scheduled event in progress.

I am looking forward to all helpful tips/comments.

>The column Days contains an integer which is a product of prime numbers corresponding to days of the week ( A value of 23 means the event occurs on Tuesday = 3 and Thursday = 7). WOuldn't the product of 3 and 7 be 21? I'm confused on your methodology. — Stephane Gosselin, May 27 '11 at 15:50
Near dup of: http://stackoverflow.com/questions/6018445/get-list-with-start-and-end-values-from-table-of-datetimes — Denis de Bernardy, May 27 '11 at 16:24

score 0 · Answer 1 · answered Aug 15 '11 at 19:02

If you're storing dates as the product of primes, the modulo (%) operator might be more useful:

SELECT * FROM Events
INNER JOIN Buildings on (Events.BuildingID = Buildings.ID)
WHERE
    (Events.Days % 2 = 0 AND Events.Days % 5 = 0)

Would select events happening on either a Monday or Wednesday.

I do have to point out though, that storing the product of primes is both computationally and storage expensive. Much easier to store the sum of powers of two (Mon = 1, Tues = 2, Wed = 4, Thurs = 8, Fri = 16, Sat = 32, Sun = 64).

The largest possible value for your current implementation is 510,510. The smallest data type to store such a number is int (32 bits per row) and retrieving the encoded data requires up to 7 modulo (%) operations.

The largest possible value for a 2^n summation method is 127 which can be stored in a tinyint (8 bits per row) and retrieving the encoded data would use bitwise and (&) which is somewhat cheaper (and therefore faster).

Probably not an issue for what you're working with, but it's a good habit to choose whatever method gives you the best space and performance efficiency lest you hit serious problems should your solution be implemented at larger scales.

Summing values of 2^n looks like a much better idea. I'm not sure why I didn't think of it in the first place. — gjarzab, Aug 16 '11 at 12:43
Yeah just noticed your only other question is on Android programming. If you're running this on Android's SQLite implementation you'll want as much efficiency as possible in your algorithm. Depending on the device, modulo can actually be extremely expensive since many embedded devices don't have actual hardware for division operations. Much better to use 2^n so you can switch to bitwise and. — David Perry, Aug 17 '11 at 17:39

SQL - Finding open spaces in a schedule

1 Answers1