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]
