calculating ratio of two datetime columns
I am selecting two columns each of type DateTime from a mysql table from
perl.
my $sth = $dbh-prepare(SELECT End_Date_Scheduled, End_Date_Actual FROM
Table1 WHERE ID='10' );
$sth-execute();
while (my $ref = $sth-fetchrow_hashref()) {
printFound: end date scheduled = $ref-{'End_Date_Scheduled'}, end
date actual = $ref-{'End_Date_Actual'}\n;
}
$sth-finish();
It works.
However, I need to find the % ratio = ((end_date_scheduled -
end_date_actual)/end_date_scheduled) * 100
To do this I need the DateTime values in seconds. Can someone help me
with this?
Best Regards
Vishwa Rao
select rows by compare on datetime column
I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao
RE: select rows by compare on datetime column
Thanks, it works now. I have related question. I am calling this from perl. my @values= $dbh-do(SELECT start_date FROM TableSLA WHERE NOW()- Start_Date = 0 ); This returns the number of rows that satisfied the condition instead of returning an array of values for start_date. Also If I have to get two column values start_date and end_date should I say: (my @values, my @values2) = $dbh-do(SELECT start_date, end_date FROM TableSLA WHERE NOW()- Start_Date = 0 ); Now the perl syntax is getting me. Any help is appreciated. Thanks Vishwa -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) = CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time = '2004-06-01 10:00:00' AND end_time = '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): -- If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) = '10:00:00' AND TIME( end_time ) = '18:00:00' -- Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) = '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select rows by compare on datetime column
I got it working, please disregard the perl related question. -Original Message- From: Viswanatha Rao [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 1:15 PM To: [EMAIL PROTECTED] Cc: 'Wesley Furgiuele'; [EMAIL PROTECTED] Subject: RE: select rows by compare on datetime column Thanks, it works now. I have related question. I am calling this from perl. my @values= $dbh-do(SELECT start_date FROM TableSLA WHERE NOW()- Start_Date = 0 ); This returns the number of rows that satisfied the condition instead of returning an array of values for start_date. Also If I have to get two column values start_date and end_date should I say: (my @values, my @values2) = $dbh-do(SELECT start_date, end_date FROM TableSLA WHERE NOW()- Start_Date = 0 ); Now the perl syntax is getting me. Any help is appreciated. Thanks Vishwa -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) = CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time = '2004-06-01 10:00:00' AND end_time = '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): -- If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) = '10:00:00' AND TIME( end_time ) = '18:00:00' -- Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) = '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select rows by compare on datetime column - more on time decrement
I have to select mysql rows based on the following condition. Time t1 = (current time - 4 hours); Time t2 = (current time - 8 hours); In my case, I want to select all those rows that have start times (1) between current time and t1 (2) between time t1 and t2 ... and so on So I have to somehow subtract hours from the current time to get t1, t2, and so on. In some other cases, I may have to subtract specific number of minutes. So my question is how do I selectively subtract hours or minutes from current time in mysql. I need these values to plot a chart. Please let me know? -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) = CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time = '2004-06-01 10:00:00' AND end_time = '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): -- If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) = '10:00:00' AND TIME( end_time ) = '18:00:00' -- Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) = '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select rows by compare on datetime column - more on time decrement
Thanks. I used SELECT Start_time, End_time FROM Table WHERE Start_time - DATE_SUB( NOW(), INTERVAL 4 HOUR ) =0 It works. -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 3:11 PM To: Viswanatha Rao Cc: [EMAIL PROTECTED] Subject: Re: select rows by compare on datetime column - more on time decrement What version of MySQL are you using? You have different options. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Look at DATE_SUB() and SUBDATE()/SUBTIME(). The time part of the query would look something like DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 4 HOUR ), '%H:%i:%s' ), or something like that. Or, this might be better handled in Perl, just using Perl to control the time value supplied in the query -- I'm not sure about your and so on. I wouldn't know how to handle a series of t1, t2, t3, t4, ..., tn without use of a scripting/programming language. Wes On Aug 18, 2004, at 3:44 PM, Viswanatha Rao wrote: I have to select mysql rows based on the following condition. Time t1 = (current time - 4 hours); Time t2 = (current time - 8 hours); In my case, I want to select all those rows that have start times (1) between current time and t1 (2) between time t1 and t2 ... and so on So I have to somehow subtract hours from the current time to get t1, t2, and so on. In some other cases, I may have to subtract specific number of minutes. So my question is how do I selectively subtract hours or minutes from current time in mysql. I need these values to plot a chart. Please let me know? -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) = CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time = '2004-06-01 10:00:00' AND end_time = '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): -- If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) = '10:00:00' AND TIME( end_time ) = '18:00:00' -- Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) = '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select rows by compare on datetime column - more on time decrement
Yes, that makes sense. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 9:05 PM To: Viswanatha Rao Cc: 'Wesley Furgiuele'; [EMAIL PROTECTED] Subject: Re: select rows by compare on datetime column - more on time decrement You don't want to do that. You are using a function of your Start_time column in your comparison. That prevents the use of any index on Start_time, so a full-table scan will be required. Always compare a column to a constant (or a range), if at all possible. Fortunately, that's easy in this case: SELECT Start_time, End_time FROM Table WHERE Start_time = NOW() - INTERVAL 4 HOUR; Michael Viswanatha Rao wrote: Thanks. I used SELECT Start_time, End_time FROM Table WHERE Start_time - DATE_SUB( NOW(), INTERVAL 4 HOUR ) =0 It works. -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 3:11 PM To: Viswanatha Rao Cc: [EMAIL PROTECTED] Subject: Re: select rows by compare on datetime column - more on time decrement What version of MySQL are you using? You have different options. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Look at DATE_SUB() and SUBDATE()/SUBTIME(). The time part of the query would look something like DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 4 HOUR ), '%H:%i:%s' ), or something like that. Or, this might be better handled in Perl, just using Perl to control the time value supplied in the query -- I'm not sure about your and so on. I wouldn't know how to handle a series of t1, t2, t3, t4, ..., tn without use of a scripting/programming language. Wes On Aug 18, 2004, at 3:44 PM, Viswanatha Rao wrote: I have to select mysql rows based on the following condition. Time t1 = (current time - 4 hours); Time t2 = (current time - 8 hours); In my case, I want to select all those rows that have start times (1) between current time and t1 (2) between time t1 and t2 ... and so on So I have to somehow subtract hours from the current time to get t1, t2, and so on. In some other cases, I may have to subtract specific number of minutes. So my question is how do I selectively subtract hours or minutes from current time in mysql. I need these values to plot a chart. Please let me know? -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) = CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time = '2004-06-01 10:00:00' AND end_time = '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): -- If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) = '10:00:00' AND TIME( end_time ) = '18:00:00' -- Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) = '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
