mysql - storing a range of values

I have a resource that has a availability field that lists what hours of a day its available for use?

eg. res1 available between 0-8,19-23 hours on a day, the range here can be comma separated values of hour ranges. e.g are 0-23 for 24 hour access, 0-5,19-23 or 0-5,12-15,19-23

What's the best way to store this one? Is char a good option? When the resource is being accessed, my php needs to check the current hour with the hour d开发者_Go百科efined here and then decide whether to allow this access or not. Can I ask mysql to tell me if the current hour is in the range specified here?

I'd store item availability in a separate table, where for each row I'd have (given your example):

id, startHour, endHour, resourceId

And I'd just use integers for the start and end times. You can then do queries against a join to see availability given a certain hour of the day using HOUR(NOW()) or what have you.

(On the other hand, I would've preferred a non-relational database like MongoDb for this kind of data)

1) create a table for resource availability, normalized.

CREATE TABLE res_avail
{
    ra_resource_id int,
    ra_start TIME,
    ra_end TIME

    # add appropriate keys for optimization here
)

2) populate with ($resource_id, '$start_time', '$end_time') for each range in your list (use explode())

3) then, you can query: (for example, PHP)

sql = "SELECT ra_resource_id FROM res_avail where ('$time' BETWEEN ra_start AND ra_end)";

....

I know this is an old question, but since v5.7 MySQL supports storing values in JSON format. This means you can store all ranges in one JSON field. This is great if you want to display opening times in your front-end using JavaScript. But it's not the best solution when you want to show all places that are currently open, because querying on a JSON field means a full table scan. But it would be okay if you only need to check on for one place at the time. For example, you load a page showing the details of one place and display whether it's open or closed.