By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

select multiple values from mysql between intervals

P: 45
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.
Attached Images
File Type: jpg db_table.jpg (55.0 KB, 231 views)
File Type: jpg db_table_report.jpg (19.0 KB, 135 views)
Jan 12 '12 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Jan 12 '12 #2

P: 45
rabbit i tried
Expand|Select|Wrap|Line Numbers
  1. SELECT MIN(`Time`) AS start_time, MAX(`Time`) AS end_time
  2. FROM table_name
  3. WHERE `Device one` = 'OFF'
  4.   AND `Device two` = 'OFF'
  5. GROUP BY `Remarks`
  6.  
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
Jan 13 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
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.
Jan 13 '12 #4

P: 45
I used mysql TIMEDIFF, it's working as intended.
so my query is like

Expand|Select|Wrap|Line Numbers
  1. "SELECT MIN(`time`) AS start_time, MAX(`time`) AS end_time,`remarks`, TIMEDIFF(MAX(`time`), MIN(`time`)) AS duration
  2. FROM `db_table`                                WHERE `time` BETWEEN '$predate' AND                                                                                                                                                                                               '$datenow'                                        AND `device one` = 'OFF'                                              AND `device two` = 'OFF'                                            GROUP BY `remarks` ORDER BY `time` DESC"
  3.  
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
Jan 16 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
Look at the execution plan and then create the appropriate indexes.
Jan 16 '12 #6

P: 45
u r right Rabbit

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

Post your reply

Sign in to post your reply or Sign up for a free account.