List:General Discussion« Previous MessageNext Message »
From:Viswanatha Rao Date:August 18 2004 7:44pm
Subject:RE: select rows by compare on datetime column - more on time decrement
View as plain text  
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
>
>

Thread
select rows by compare on datetime columnViswanatha Rao18 Aug
  • Re: select rows by compare on datetime columnWesley Furgiuele18 Aug
    • Re: select rows by compare on datetime columnWesley Furgiuele18 Aug
      • RE: select rows by compare on datetime columnViswanatha Rao18 Aug
        • RE: select rows by compare on datetime columnViswanatha Rao18 Aug
      • RE: select rows by compare on datetime column - more on time decrementViswanatha Rao18 Aug
        • Re: select rows by compare on datetime column - more on time decrementWesley Furgiuele18 Aug
          • RE: select rows by compare on datetime column - more on time decrementViswanatha Rao19 Aug
            • Re: select rows by compare on datetime column - more on time decrementMichael Stassen19 Aug
              • RE: select rows by compare on datetime column - more on time decrementViswanatha Rao19 Aug
  • Re: select rows by compare on datetime columnSGreen18 Aug