Select based on day of week and time of day

Databases
1

Hi

I have a mysql database table that contains a list of radio shows for a local radio station. Each show is on for a few hours (I.e. Has a start time and an end time) and maybe on, on a specific day (I.e. Mon, tues, weds, etc) or all weekdays. Usually the shows that are on on a weekday are the same every weekday but their are different shows on on Saturday and Sunday.

I have an area on the website that shows what show is current on/playing.

So far I have been able to create SQL statement that is able to find the right show for the current day and current time (for weekday shows) but I have a problem finding the right show at the weekend.

I’ve added a field “shDay” which is an integer storing which day of the week the show is on (I.e. Sunday = 1, Monday = 2, etc) I’ve also used a value of 10 if the show is on every weekday.

What I need is for the SQL select statement to check what day of the week it is, what the current time is and then display the correct show that should be playing (I.e. If it’s 10:30am on a Tuesday then the correct show would the show that plays (start time) 09:00am to (end time) 12:00pm every weekday (or maybe just on Tuesday’s). If it’s a weekend then it must ignore the weekday only shows (where shDay = 10) and obviously only choose shows pertaining to the correct weekend day (where shDay might be either 7 (sat) or 1 (sun)).

My (relevant) fields are:
ShTitle - title of the show
ShDay - INT for which day this show is on 1 to 7 for sun to sat or 10 for every weekday
ShStartTime and shEndTime - the start and end times of the show. Stored as VARCHAR in the format of e.g. 09:00:00 (for 9:00am)

This is what I have currently which fine for the weekday shows but at the weekend it still lists the weekday only shows rather than only the weekend shows (because of the OR shDay = 10 bit):

dayvariable = (DatePart(“w”,Date()))

"SELECT shId, shTitle, shStartTime, shEndTime, shDay FROM tblshows WHERE shEnabled = ‘Y’ AND (shDay = " &*dayvariable & “*OR shDay = 10) AND ((time(concat( CurDate(), ’ ', shStartTime ))) < CurTime() AND (time(concat( CurDate(), ’ ', shEndTime ))) > CurTime()) ORDER BY shStartTime ASC LIMIT 0,1”

(by the way I’ve only referenced an external variable “day variable” because I don’t know how to select/use a day of week variable directly within MySql)

Hope someone can help,

Many thanks.

2

does 10 mean every weekday (2 through 6) or every day of the week (1 through 7)?

draft query, pending answer to the above…

SELECT shId
     , shTitle
     , shStartTime
     , shEndTime
     , shDay 
  FROM tblshows 
 WHERE shEnabled = 'Y' 
   AND (
       shDay = DAYOFWEEK(CURRENT_DATE)
    OR shDay = 10
       ) 
   AND CURRENT_TIMESTAMP 
       BETWEEN CURRENT_DATE + INTERVAL 
                 TIME_TO_SEC(CAST(shStartTime AS TIME)) SECOND
           AND CURRENT_DATE + INTERVAL 
                 TIME_TO_SEC(CAST(shEndTime AS TIME)) SECOND
ORDER 
    BY shStartTime ASC LIMIT 0,1
3

Hi

10 is every weekday 2-6 (mon to fri)

Thanks

4 
 WHERE shEnabled = 'Y' 
   AND (
       shDay = DAYOFWEEK(CURRENT_DATE)
    OR shDay = 10  
           AND DAYOFWEEK(CURRENT_DATE)
               BETWEEN 2 AND 6        
       ) 
   AND CURRENT_TIMESTAMP 
       BETWEEN ...
5

Thank you very I’ll go and give that a go. Many thanks for such a speedy response.