Yes, that makes sense.
-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@stripped]
Sent: Wednesday, August 18, 2004 9:05 PM
To: Viswanatha Rao
Cc: 'Wesley Furgiuele'; mysql@stripped
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:wesley@stripped]
> Sent: Wednesday, August 18, 2004 3:11 PM
> To: Viswanatha Rao
> Cc: mysql@stripped
> 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:wesley@stripped]
>>Sent: Wednesday, August 18, 2004 11:36 AM
>>To: Viswanatha Rao
>>Cc: mysql@stripped 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/mysql?unsub=1
