select multiple values from mysql between intervals

[charles07]

hi all

 

please help...

 

i would like to get datas between certain intervals.

please have a look @ db_table.jpg, it's the db structure. the red highlighted part is the value i need.

 

condition is select values when both device one & device two are off.

 

db_table_report.jpg is the resulting report format. It contains start time & end time and the duration..

 

please help, i know it's a bit complicated, but it's urgent.

[sunfighter]

Your query is:

select * from database where Device one = "off" and Device two = "off"

[Psycho]

I don't think that is exactly what the OP needed. The explanation is pretty poor.

 

What I *think* you need is the MIN() and MAX() of the "Time" for each "remark" value that has one or more records where "Device one" and "Device two" are both off.

 

By the way I would suggest renaming your fields to not have spaces in them. Either remove the space entirely or use an underscore. It will save you problems later. Also, don't use the strings "ON" and "OFF" as you would have to always do string comparisons on them. Instead use 0/1 which can be evaluated as the logical True/False

 

Anyway, I think this will give you what you need:

SELECT MIN(`Time`) AS start_time, MAX(`Time`) AS end_time
FROM table_name
WHERE `Device one` = 'OFF'
  AND `Device two` = 'OFF'
GROUP BY `Remarks`
ORDER BY `Remarks`

[charles07]

thank you  Psycho

 

that really helped, bu t still there is a hurdle.

 

how do i get the duration of time i.e. difference between resulting two days 2011-12-03 22:06:42 and 2011-12-06 16:18:14

 

i am playing with mysql datedif & subtime functions, but no results yet. datediff gives resulting days and subtime gives need input as time no dates allowed before, and all these should work in less time

[Psycho]

I'm sure there is a MySQL method to get that, but it might be easier to just do that calculation in the PHP code that processes the records

 

$query = "SELECT MIN(`Time`) AS start_time, MAX(`Time`) AS end_time
          FROM table_name
          WHERE `Device one` = 'OFF'
            AND `Device two` = 'OFF'
          GROUP BY `Remarks`
          ORDER BY `Remarks`";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    $time_diff_in_sec = strtotime($row['end_time']) - strtotime(['start_time']);

    //Do something with the results
}

[mikosiko]

I'm sure there is a MySQL method to get that....

 

 

yes it is

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

[Psycho]

I'm sure there is a MySQL method to get that....

 

 

yes it is

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

 

Yeah, I saw that, but the value returned is in the format '46:58:57.999999' which would still require PHP functionality to convert that into a usable format. So, I'd still stick with doing the calculation in PHP.

[charles07]

thanks  psycho & mikosiko

 

I used mysql TIMEDIFF, it's working as intended.

 

so my query is like

 

"SELECT MIN(`time`) AS start_time, MAX(`time`) AS end_time,`remarks`, TIMEDIFF(MAX(`time`), MIN(`time`)) AS duration
										FROM `db_table`
										WHERE `time` BETWEEN '$predate' AND                                                                                                               
                                                                                        '$datenow'
										AND `device one` = 'OFF'
										  AND `device two` = 'OFF'
										GROUP BY `remarks` ORDER BY `time` DESC"

 

this gives the needed result,

but is there any way to speed up the process, now  it takes around 10 seconds to load the result

[Psycho]

Try taking out the timediff() part of the query and do the calculation in PHP. Also, what is the purpose of the ORDER BY `time` since time isn't even in the result set?

 

Also, as I stated before, you should NOT be using 'ON'/'OFF' for the `device one` and `device two` field values. You should change those fields to tiny ints and use 0 and 1. Those values can logically be interpreted as true/false and it will be much faster for the MySQL engine to do those comparisons.

[charles07]

u r right Psycho

 

i am changing 'on',  'off' varchar fields to tinyint numeric 1 or 0. there is total 450000+ rows. thanks Psycho