I'm reading opening hours for libraries from a database, so I get returned something like:
{
"5":{ // library ID
"4":{ // interval ID
"statement":"",
"start_date":"5th Jan 2015",
"end_date":"1st Mar 2015",
"hours":[
{
"day_of_week":"Sun",
"opening_time":"12am",
"closing_time":"12am",
"is_closed":true,
"open_24hrs":false
},
{
"day_of_week":"Mon",
"opening_time":"9am",
"closing_time":"5pm",
"is_closed":false,
"open_24hrs":false
},
{
"day_of_week":"Tue",
"opening_time":"9am",
"closing_time":"5pm",
"is_closed":false,
"open_24hrs":false
},
for each "regular" opening hours and the same for each "variation", where the interval may be anything from one day to seven days. (here I've already preprocessed the day numbers to short day names). I'm getting the data from PostGreSQL via AJAX/PHP. My question is, how can I properly order the days for variations when the intervals may be anything from one day to several weeks (but only hours for one week are stored for intervals one week or longer), and can begin at any day eg: Fri, Sat, Sun, Mon long weekend (in this case the day numbers are 5,6,0,1 and so come out ordered as Sun, Mon, Fri, Sat)?
My initial thoughts are:
- check if interval is a full week
- if not, check if Sunday is traversed
- if not, easily order by number
- if Sunday is traversed, get start day and order up to Sunday then reset to 0 (for Sunday) and continue to highest number before start day
But I'm wondering if this is all overkill and I can accomplish the same using an order by clause or similar, something like this: Possible to create a query that sorts by day of week? ?
Any thoughts much appreciated.
