CODEWITHSUNDEEP
mysqlruby-on-railsrubymath
bricker
bricker

Reputation: 8941

Query to find records where a date is between start and end dates, where the dates might be reset to 0

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!

Upvotes: 0

Views: 596

Answers (3)

Michael
Michael

Reputation: 10474

You should store your time as a timestamp and just calculate the start_time of every week and do a little math. This prevents the problems you are describing, then you:

select * from events where (start_time < SUNDAYS_TIMESTAMP and end_time > SUNDAYS_TIMESTAMP or whatever you really want to try.

You should store your data in a universal format and figure out what you want from that.

Upvotes: 0

dan
dan

Reputation: 13272

You can try something like:

select * from events 
   where (start_time <= 3600 or start_time>end_time) and end_time > 3600;

This way you will be able to catch the events that were started before your rest time and are still happening at the specified time.

Upvotes: 1

Tom
Tom

Reputation: 334

You could just make the start time very high.

SELECT * FROM events WHERE start_time <= 590000 AND end_time > 3600

Upvotes: 0

Related Questions