SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select based on day of week and time of day

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does 10 mean every weekday (2 through 6) or every day of the week (1 through 7)?

    draft query, pending answer to the above...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

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

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
     WHERE shEnabled = 'Y' 
       AND (
           shDay = DAYOFWEEK(CURRENT_DATE)
        OR shDay = 10  
               AND DAYOFWEEK(CURRENT_DATE)
                   BETWEEN 2 AND 6        
           ) 
       AND CURRENT_TIMESTAMP 
           BETWEEN ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very I'll go and give that a go. Many thanks for such a speedy response.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •