We have weekly recurring events in our application, and have decided to store the start and end times for each event as the number of seconds from the beginning of the week (Sunday 12am). This makes querying, sorting, etc. much easier.
However, there is one small problem: If we want to get the event that is happening at a time near the week reset, that event could bridge that gap, and it makes querying difficult.
A normal query, which works 99% of the time, to grab the records where the given time is between the record's start and end time.
time = 216000 # Tuesday, 12:00PM
SELECT * FROM events WHERE start_time <= 216000 AND end_time > 216000
The hard part is when there is an event that starts at, say, Saturday at 11pm, and ends on Sunday at 2am:
time = 3600 # Sunday, 1am
SELECT * FROM events WHERE start_time <= 3600 AND end_time > 3600
This won't return anything because the start_time
will NOT be less than 3600, since it starts near the end of the weekly cycle.
I've been racking my brain for a few hours trying to come up with a good solution, but have fallen short so far, so I'd like to open it up to SO to see if there is solution that I'm not thinking of. I have a feeling that there is some simple math equation I can use to get this done, but math isn't my best subject :)
Please let me know if I need to explain better or give better examples.
Thanks!
TIME
column, and the day of the week in anINT
column, but the "seconds-since-beginning-of-week" is practically the same thing.WHERE (end_time < start_time) AND ...
, but couldn't figure out the...
bit.