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]
