Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2010
    Posts
    39

    Unanswered: How to show the current programs ?

    Hello friends I have a table 'programs'

    PHP Code:
    CREATE TABLE `image_db`.`programs` (
    `
    idINT11 UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `
    program_nameVARCHAR50 CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `
    timeDATETIME NOT NULL ,
    `
    dayVARCHAR40 NOT NULL
    ENGINE InnoDB
    How do i show the programs that is currently going on air according to the information on the 'program' table ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    make life easier for yourself by storing values in thier native dataype. that means dates and times in the datetime datatype. doing so means you can use the date time functions built into MySQL to manipulate the data

    you don't need separate columns for date and time, you shouldn't store dates as text/varchar

    I would expect a program to have a duration whether you express that as a start & end datetime or a start date & duration time is up to you. however if you want to find what program is on right here right now you will make it easier to use a start & end datetime. you can always derive the duration using the appropriate function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    39
    Thanks healdem,

    Could you please suggest me fields. Thanks

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try something along the lines of

    Code:
    CREATE TABLE `image_db`.`programs` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `program_name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `time` DATETIME NOT NULL ,
    `duration` INT NOT NULL
    ) ENGINE = InnoDB;
    The duration you will need to decide whether this represents minutes or seconds or hours. To get your programs running at the moment (I am assuming duration in SECONDs):

    Code:
    SELECT program_name
    FROM   programs
    WHERE  TIME < NOW()
           AND DATE_ADD(TIME, INTERVAL duration SECOND) > NOW()
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by linux1880 View Post
    Thanks healdem,

    Could you please suggest me fields. Thanks
    I thought I already had..

    I'd follow It-iss's appraoch, except...
    I'd want to store the date time a proramme starts and the date time a programme ends

    why?
    well I don't think the duration is as meaningful as the end time. using end time simplifies the SQL to find a current program..

    Code:
    SELECT My, Column, List FROM mytable
    WHERE StartsAt  >= now() and Now()<=EndsAt;
    or better yet try the between construct.

    Code:
    SELECT My, Column, List FROM mytable
    WHERE now() BETWEEN StartsAt  AND EndsAt;
    if you use the BETWEEN construct you must make certain the earliest date is shown first otherwise the results can be 'peculiar'

    I'd probably name those columns StartsAt and EndsAt or somethign similar, both would be datetime values. however naming columns is always fraught with potential issues.. the name should meet your naming convention, it should adequately, uniquely and immediately describe the purpose of that column not just to you but other developers or consumers of your data. ideally it should be like reading a normal sentence. so for me calling a column 'time' is not ideal, time of what?, calling it StartsAt makes the meaning very clear, its the time the programmer starts at. with reference to the table name aswell its crystal clear
    Code:
    SELECT PROGRAMME.StartsAt, PROGRAMME.EndsAt FROM PROGRAMME
    Personally I use 'CamelCase' where the first letter of each word comprising the column name is uppercase, others use all lower case. I use camel case as I find it makes it easier on the eye to read, but you may feel different or not have that choice.

    By storing a start & end time you no longer are storing the duration, but thats not an issue as the duration is derived. To find the duration of a program use the TimeDiff function, or use an appropriate function in the front end / code driving this application.

    essentially it doesn't matter whether you store starttime and a duration or start time and an end time (or for that matter a duration and end time), if you know two pieces of information you can derive the third piece. I prefer the StartsAt / EndsAt approach as it makes the SQL neater and more legible (in my books) but essentially its irrelevant. the only thing is you don't store all 3 elements. To mangle Orwell's Animal Farm: "two elements good, three elements bad"
    Last edited by healdem; 03-16-11 at 10:25.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2010
    Posts
    39
    Thank you all, I will try it now

  7. #7
    Join Date
    Jul 2010
    Posts
    39
    Is there a way to list all todays programs as well ?

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Yes, you can always create a timestamp based on a concatenation of the date and and 00:00:00 and 23:59:59 and then find the records that are found in between and including this range.

    It is more efficient converting the current date to a date format than converting the date in each of your records to a string as the conversion is relatively expensive in CPU terms.

    Code:
    mysql> select str_to_date(concat(curdate(),' 00:00:00'),'%Y-%m-%d %H:%i:%s');
    +----------------------------------------------------------------+
    | str_to_date(concat(curdate(),' 00:00:00'),'%Y-%m-%d %H:%i:%s') |
    +----------------------------------------------------------------+
    | 2011-03-20 00:00:00                                            | 
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select str_to_date(concat(curdate(),' 23:59:59'),'%Y-%m-%d %H:%i:%s');
    +----------------------------------------------------------------+
    | str_to_date(concat(curdate(),' 23:59:59'),'%Y-%m-%d %H:%i:%s') |
    +----------------------------------------------------------------+
    | 2011-03-20 23:59:59                                            | 
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Jul 2010
    Posts
    39
    Thanks it-iss.com, actually i have changed fields like this

    Code:
    CREATE TABLE `programs` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `program_name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `start_time` DATETIME NOT NULL  default 0000-00-00 00:00:00,
    `end_time` DATETIME NOT NULL default 0000-00-00 00:00:00,
    ) ENGINE = InnoDB;
    just wondering how do i get like 'Today's programs'. I did check mysql date function but couldn't find.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    intrinsically its not that different from finding a current program, except you need to find a way of stripping out the time element

    select My, Ccolumn, List from MyTable
    where Date(Now()) between date(StartTime) and date(EndTime)

    Havbe a look at thre MySQL date time functions
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jul 2010
    Posts
    39
    Can anybody please give me working example ? Thanks

  12. #12
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You have all the information you need to at least have a go at writing the SQL. If this does not work post up what you have and we can help you from there.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  13. #13
    Join Date
    Jul 2010
    Posts
    39
    I did try this but no result
    SELECT * FROM `programs`
    where str_to_date(concat(curdate(),' 00:00:00'),'%Y-%m-%d %H:%i:%s') = start_time
    i did also try but no luck
    SELECT * FROM `programs`
    where
    curdate() between start_time and end_time
    also tried no luck
    SELECT * FROM `programs`
    where
    concat(curdate(),' ', '00:00:00')
    between start_time and end_time
    Last edited by linux1880; 03-21-11 at 04:15.

  14. #14
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When you say you want the programs of today. Do you mean where the program starts today and/or if the program ends today?

    I suspect it will be all programs that start on a particular day. So your start time should be between today's date 00:00:00 and today's date 23:59:59.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you look at the MySQL date / time functions?
    if so what made you decide to use concat?
    did you try the date(now()) between date(starttime) and date(endtime)?
    if that didn't work what stopped it working? if that didn't work then you may need to fall back on the
    where date(now()) >= date(StartTime) and date(now()) <= date(EndTime)

    did you analyse why the things you tried in your post #13 didnt' work, if so what did you learn from that?

    arguably in place of now() you coudl use the current date function in MySQL.. forget what it is at present, but noi foubt its listed int he Dat / time functions referred to in post #2


    if you don't understand what a sample piece of SQL or code is trying to do then use an approprate tool or debugging breakpoint to learn
    for SQL you probably need somehtign like Heidi SQL, MySQL workbench or even somehting like PHPMyAdmin.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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