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.

[Rabbit]

Do a min and max aggregate query grouping by the remark and that will give you the start and end. Then you can use the datediff function to calculate the duration.

[charles07]

rabbit i tried

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`
 

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

my date format is like 2011-12-03 22:06:42 and 2011-12-06 16:18:14

[Rabbit]

Well, you have to use DateDiff to get the number of minutes between the two dates and then calculate the elapsed days, hours, and minutes yourself.

[charles07]

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

[Rabbit]

Look at the execution plan and then create the appropriate indexes.

[charles07]

u r right Rabbit

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