How to show the current programs ?

linux1880 · #1 (**permalink**) 03-15-11, 23:27

Hello friends I have a table 'programs'

PHP 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 ,

`day` VARCHAR( 40 ) NOT NULL

) ENGINE = InnoDB;

How do i show the programs that is currently going on air according to the information on the 'program' table ?

healdem · #2 (**permalink**) 03-16-11, 03:56

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

linux1880 · #3 (**permalink**) 03-16-11, 04:06

Thanks healdem,

Could you please suggest me fields. Thanks

it-iss.com · #4 (**permalink**) 03-16-11, 04:37

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()

healdem · #5 (**permalink**) 03-16-11, 06:19

Quote:

Originally Posted by linux1880

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"

linux1880 · #6 (**permalink**) 03-16-11, 08:41

Thank you all, I will try it now

linux1880 · #7 (**permalink**) 03-20-11, 05:43

Is there a way to list all todays programs as well ?

it-iss.com · #8 (**permalink**) 03-20-11, 07:37

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)

linux1880 · #9 (**permalink**) 03-20-11, 11:20

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.

healdem · #10 (**permalink**) 03-20-11, 11:46

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

linux1880 · #11 (**permalink**) 03-20-11, 19:13

Can anybody please give me working example ? Thanks

it-iss.com · #12 (**permalink**) 03-20-11, 19:19

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.

linux1880 · #13 (**permalink**) 03-21-11, 02:46

I did try this but no result

Quote:

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

Quote:

SELECT * FROM `programs`
where
curdate() between start_time and end_time

also tried no luck

Quote:

SELECT * FROM `programs`
where
concat(curdate(),' ', '00:00:00')
between start_time and end_time

it-iss.com · #14 (**permalink**) 03-21-11, 04:21

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.

healdem · #15 (**permalink**) 03-21-11, 05:11

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech