Finding available timeslots between dates

Question

I am creating a REST API for a booking calendar, and right now I am trying to figure out the most efficient way of writing a query that returns all timestamps between two dates with a 15 minute interval. If I supply2013-09-21 and 2013-09-22 I would like to get:

2013-09-21 00:15:00
2013-09-21 00:30:00
2013-09-21 00:45:00
2013-09-21 01:00:00
2013-09-21 01:15:00
2013-09-21 01:30:00
...
2013-09-22 23:15:00
2013-09-22 23:30:00
2013-09-22 23:45:00

I would then use this query as a subquery and apply some conditions on it to remove timeslots outside working hours (which are not constant), booked timeslots, etc.

I have seen a lot of blog posts where the author creates a "calendar table" which stores all these timestamps, but that seems like a waste to me since that data doesn't need to be stored.

Any suggestions on how I could do this or a better way to fetch/store the data?

Store the time as a [UNIX timestamp](http://en.wikipedia.org/wiki/unix%20timestamp) (nothing but an integer) with intervals of 900 (15 x 60 seconds). When retrieving the data use the [from_unixtimestamp](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime) function to get a human readable date. — Nikhil, Sep 30 '13 at 07:40
That is possible sure, but if it is possible to write a query that creates this data on-the-fly, then that would be preferred. — Samuel Lindblom, Sep 30 '13 at 07:43
http://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql. Maybe you can tweak this to generate values in steps. — Nikhil, Sep 30 '13 at 07:48
Consider handling that logic in the application level/presentation layer code. — Strawberry, Sep 30 '13 at 09:23
@Strawberry: You might be right, and I can output this from server-side code without problems. But MySQL is much better at the filtering stuff that comes after, so I'll wait and see if someone suggests a more elegant solution. — Samuel Lindblom, Sep 30 '13 at 10:20
Someone may suggest an alternative, but it could not be 'more elegant' ;-) — Strawberry, Sep 30 '13 at 10:22
I agree with Strawberry: Assuming you want to display something calendar-like, the DB should store timestamps that are in use, the server-side code should query timestamps within a given range and display a "page" of results, populating each "slot" with info based on whether a timestamp was found for that slot. In this example, "page" could equate to "month" and "slot" could equate to "day". — kmoser, Sep 30 '13 at 15:41

score 0 · Answer 1 · edited May 23 '17 at 10:25

Here is a process that generates 95 rows incrementing a date variable as it goes and then left join the table with the dated entries to the "solid" table that has generated dated rows.

select str_to_date('2010-01-01', '%Y-%m-%d') into @ad;

select * from

(select (@ad := date_add(@ad, INTERVAL 15 MINUTE)) as solid_date from wp_posts limit 95) solid
left join
wp_posts
  on solid.solid_date = post_date

I've no idea how to generate an arbitrary number of rows in mysql so i'm just selecting from a table with more than 95 rows (24 hours * 4 appointments per hour less one at midnight) -- my wordpress posts table. Nothing stopping you making just such a table and having a single column with a single incrementing integer in if there are no better ways to do it (i'm an oracle guru not a mysql one). Maybe there isn't one: How do I make a row generator in MySQL?

Where you see wp_posts, substitute the name of your appointments table. Where you see the date, substitute your start date.

The query above produces a list of dates starting 15 after midnight on the chosen day (in my example 2010-01-01)

You can add a WHERE appointments.primary_key_column_here IS NULL if you want to find free slots only

Note you didn't include midnight in your spec. If you want midnight on the first day, start the date in the variable 15 minutes before and limit yourself to 96 rows. If you want midnight on the end day, just limit yourself to 96 rows

Finding available timeslots between dates

1 Answers1