mysql - count consecutive days where temp is below 0 -

i trying count of consecutive days in db temperature below 0. can total count of total days below 0 using select count not consecutive number of days. want able show first , last day count. table updated every minute.

assume have minimal table such as:

    datetime            temp     11/14/2014 7:21:31  -2.4     11/14/2014 7:22:31  -2.4     11/15/2014 5:03:31  2.4     11/15/2014 5:04:31  2.4     11/16/2014 5:10:31  -0.2     11/16/2014 5:11:31  -0.2     11/17/2014 5:13:31  -0.2     11/17/2014 5:14:31  -0.2     11/18/2014 5:15:31  2     11/18/2014 5:16:31  2 

in example, consecutive days 2, first date 11/16/2014 , last date 11/17/2014 , total days (i can this) 3.

thanks looking.

edit: want longest consecutive streak. how got data, simple select statement: select datetime, temp mytable;

consider following...

the data set...

 drop table if exists my_table;   create table my_table  ( dt datetime not null primary key  , temp decimal(5,2) not null);   insert my_table values  ('2014-11-14 7:21:31',  -2.4),  ('2014-11-14 7:22:31',  -2.4),  ('2014-11-15 5:03:31',  2.4),  ('2014-11-15 5:04:31',  2.4),  ('2014-11-16 5:10:31',  -0.2),  ('2014-11-16 5:11:31',  -0.2),  ('2014-11-17 5:13:31',  -0.2),  ('2014-11-17 5:14:31',  -0.2),  ('2014-11-18 5:15:31',  2),  ('2014-11-18 5:16:31',  2); 

solution 1 - if want know length of longest run...

 select max(@count := if(a.result = b.result, @count + 1, 1)) longestrun           ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       )   cross    join (select @count := 0) vars    left    join        ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       ) b      on b.date = a.date - interval 1 day    a.result = 1;    +------------+  | longestrun |  +------------+  |          2 |  +------------+ 

solution 2 - if want of other information too

 select a.date start       , min(c.date) end       , datediff(min(c.date),a.date) + 1 longestrun          ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       )    left     join        ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       ) b       on b.date = a.date - interval 1 day     , b.result = 1    left     join        ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       ) c       on c.date >= a.date      , c.result = 1    left     join        ( select distinct date(x.dt) date                       , coalesce(y.temp,x.temp) < 0 result                     my_table x                     left                     join my_table y                       on date(y.dt) = date(x.dt) , y.temp < 0       ) d       on d.date = c.date + interval 1 day     , d.result = 1   a.result = 1     , b.date null     , c.date not null     , d.date null   group       a.date   order       longestrun desc    limit 1;    +------------+------------+------------+   | start      | end        | longestrun |   +------------+------------+------------+   | 2014-11-16 | 2014-11-17 |          2 |   +------------+------------+------------+ 

for aggregate data, issue separate query.