See if this is the expected result:
1- Find the shortest date after the value is changed. Ex:
23.77 2017-05-01 21:40:12
23.93 2017-05-01 21:40:13
there was a change in value, so the next date to the value 23.77 is 2017-05-01 21:40:13
Code:
(select top 1
x.data
from medicoes x
where x.valor != m.valor
and x.data > m.data
order by x.data)
2 - Find the lowest date on which that value occurred, grouping by the last date obtained previously, and already calculating the duration in seconds between the first and last date.
Code:
select
y.dia,
min(y.data) as primeira_data,
y.valor,
y.ultima_data,
datediff(second, min(y.data),y.ultima_data) as duracao
from
(select
cast(m.data as date) as dia,
m.data,
m.valor,
(select top 1
x.data
from medicoes x
where x.valor != m.valor
and x.data > m.data
order by x.data) as ultima_data
from medicoes m) y
group by y.dia, y.valor, y.ultima_data
3 - List each day, and add the duration of each value, thus having the expected result.
Code:
select
x.dia,
x.valor,
sum(coalesce(x.duracao,0)) as duracao_total_segundos
from
(select
y.dia,
min(y.data) as primeira_data,
y.valor,
y.ultima_data,
datediff(second, min(y.data),y.ultima_data) as duracao
from
(select
cast(m.data as date) as dia,
m.data,
m.valor,
(select top 1
x.data
from medicoes x
where x.valor != m.valor
and x.data > m.data
order by x.data) as ultima_data
from medicoes m) y
group by y.dia, y.valor, y.ultima_data) x
group by x.dia, x.valor
order by x.dia, x.valor
Note: I put a Coalesce in the sum of the duration, because if there is no date greater than the date of the record, the return of the duration will be null, causing the sum to return null as well. So, the last record in the table (by date order) will always have duration 0 because it can not be determined when that value has changed.
See in SqlFiddle:
link
Result:
dia valor duracao_total_segundos
2017-05-01 7.11 2
2017-05-01 7.32 1
2017-05-01 7.34 19
2017-05-01 7.36 1
2017-05-01 7.38 1
2017-05-01 7.39 1
2017-05-01 7.4 2
2017-05-01 7.41 1
2017-05-01 7.47 27
2017-05-01 7.54 10727
2017-05-01 23.68 0
2017-05-01 23.73 2
2017-05-01 23.77 3
2017-05-01 23.78 1
2017-05-01 23.82 1
2017-05-01 23.85 2
2017-05-01 23.87 2
2017-05-01 23.93 1
2017-05-01 23.94 1
2017-05-01 24.07 3
2017-05-01 24.15 1
Edit: I noticed after you asked for some filters and other options, type: show the shift, and the time in minutes or hours, then ...
Code updated and changed for MYSQL:
select
x.dia,
x.valor,
x.turno,
sum(coalesce(x.duracao,0)) as duracao_total_segundos,
sum(coalesce(x.duracao,0))/60.0 as duracao_total_minutos,
sum(coalesce(x.duracao,0))/60.0/60.0 as duracao_total_horas
from
(select
y.dia,
min(y.data) as primeira_data,
y.valor,
y.ultima_data,
(UNIX_TIMESTAMP(y.ultima_data) - UNIX_TIMESTAMP(min(y.data))) as duracao,
y.turno
from
(select
cast(m.data as date) as dia,
m.data,
m.valor,
(select
min(x.data)
from medicoes x
where x.valor != m.valor
and x.data > m.data
) as ultima_data,
(case
when hour(m.DATA)<12 then 'Manhã'
when hour(m.DATA)<18 then 'Tarde'
else 'Noite'
end ) as turno
from medicoes m) y
group by y.dia, y.valor, y.ultima_data,y.turno) x
group by x.dia, x.valor, x.turno
order by x.dia, x.valor;
I updated SQLFiddle: link (Note The result is the last query
Where, I leave you.
I put some more data in the fiddle to get results with different shifts.
Result:
dia valor turno duracao_total_segundos duracao_total_minutos duracao_total_horas
2017-05-01 0 Manhã 10797 179.95 2.9991666666
2017-05-01 0 Tarde 17942 299.033333 4.9838888833
2017-05-01 7.11 Manhã 3 0.05 0.0008333333
2017-05-01 7.11 Noite 2 0.033333 0.00055555
2017-05-01 7.11 Tarde 3 0.05 0.0008333333
2017-05-01 7.32 Noite 1 0.016666 0.0002777666
2017-05-01 7.34 Noite 19 0.316666 0.0052777666
2017-05-01 7.36 Noite 1 0.016666 0.0002777666
2017-05-01 7.38 Noite 1 0.016666 0.0002777666
2017-05-01 7.39 Noite 1 0.016666 0.0002777666
2017-05-01 7.4 Noite 2 0.033333 0.00055555
2017-05-01 7.41 Noite 1 0.016666 0.0002777666
2017-05-01 7.47 Noite 27 0.45 0.0075
2017-05-01 7.54 Noite 10727 178.783333 2.9797222166
2017-05-01 23.68 Noite 0 0 0
2017-05-01 23.73 Noite 2 0.033333 0.00055555
2017-05-01 23.77 Noite 3 0.05 0.0008333333
2017-05-01 23.78 Noite 1 0.016666 0.0002777666
2017-05-01 23.82 Noite 1 0.016666 0.0002777666
2017-05-01 23.85 Noite 2 0.033333 0.00055555
2017-05-01 23.87 Noite 2 0.033333 0.00055555
2017-05-01 23.93 Noite 1 0.016666 0.0002777666
2017-05-01 23.94 Noite 1 0.016666 0.0002777666
2017-05-01 24.07 Noite 3 0.05 0.0008333333
2017-05-01 24.15 Noite 1 0.016666 0.0002777666
