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

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