0

I have this sql table

     temp_int   hum_int   datetime             
      25.9        84   2013-10-01 11:30:00  
      26.4        81   2013-10-01 11:45:00  
      25.3      88.1   2013-10-02 11:00:00  
      26.3      85.8   2013-10-02 11:15:00  
      27.1      83.5   2013-10-02 11:30:00  
      27.9      81.9   2013-10-02 11:45:00  
      28.8        81   2013-10-02 12:00:00  
      26.1      80.3   2013-10-02 19:15:00  
      25.8      81.6   2013-10-02 19:30:00

So I want count number of times the condition is exceeded for 45 minutes. The condition is temp_int>=25 and hum_int>80, so for this example dataset must give me 1 as a result:

First one= 2013-10-02 11:00:00

Second one= 2013-10-02 11:45:00

For instance:

enter image description here

For this set, mysql must return one row, for instance when start the time window.

ManuParra
  • 1,471
  • 6
  • 18
  • 33
  • I presume you can change the SQL or whatever generates this to remove all the extraneous formatting and make a simple CSV? – Mark Setchell Feb 16 '14 at 12:04
  • Ok, is not a Specific data format is mysql query data result – ManuParra Feb 16 '14 at 12:28
  • Manu, every row in your example data is over those thresholds? In your real data is there rows at every 15 minute interval? – OGHaza Feb 16 '14 at 13:26
  • Hi @OGHaza there are a lot of rows, each 15 minutes, every hour, day, .. – ManuParra Feb 16 '14 at 14:05
  • Ok, and say at `00:00` they go over the threshold, then they stay above the threshold until `02:00`, what time values would you expect in the result set? – OGHaza Feb 16 '14 at 14:07
  • Each 15 minutes I've a datalog register. But I need how much times happened into a time interval of 45 minutes continuous. – ManuParra Feb 16 '14 at 14:17

1 Answers1

0

to accomplish this i had to create extra column ID to let it easy to handle between rows , you should also done this. and fill it with values like that : 

id  temp_int   hum_int   datetime             
 1    25.9        84   2013-10-01 11:30:00  
 2    26.4        81   2013-10-01 11:45:00  
 3    25.3      88.1   2013-10-02 11:00:00  
 4    26.3      85.8   2013-10-02 11:15:00  
 5    27.1      83.5   2013-10-02 11:30:00  
 6    27.9      81.9   2013-10-02 11:45:00  
 7    28.8        81   2013-10-02 12:00:00  
 8    26.1      80.3   2013-10-02 19:15:00  
 9    25.8      81.6   2013-10-02 19:30:00

and then run this query

select  t1.`datetime`
from contacts t1
inner join contacts t2
on t2.id = (t1.id + 1)
where TIMESTAMPDIFF(MINUTE,t1.`datetime`,t2.`datetime`) > 45
and t1.temp_int>=25 and t1.hum_int>80

WATCH DEMO HERE

echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • It doesnt do what It has to do. As you see: at 2013-10-02 11:00:00 start the restriction (temp_init, hum_int) and it continues 45 minutes and then it continues. I need know how many times of 45 minutes groups have happend. – ManuParra Feb 16 '14 at 14:15
  • i dont understand how you count and how it must be the restriction. explain well pls – echo_Me Feb 16 '14 at 14:22
  • Well, I have a time serie, for instance: 2013-10-02 11:00:00, 2013-10-02 11:15:00, 2013-10-02 11:30:00, 2013-10-02 11:45:00, 2013-10-03 11:00:00, 2013-10-03 11:15:00,... And I know that this serie has a 45 minutes continuous group from 2013-10-02 11:00:00 to 2013-10-02 11:45:00, because the next data in time serie is another day (so, not can be continuous) – ManuParra Feb 16 '14 at 14:28