0
I need to group data in a 15-minute interval, giving me the total number of vehicles in a given date range. What I got so far was this query:
SELECT Date_format(data_hora, "%y-%m-%d") AS dataFormat,
Date_format(( Sec_to_time(Time_to_sec(data_hora) -
Time_to_sec(data_hora)%( 15 *
60 )) ), "%h:%i") AS
intervals,
eq_id,
Sum(online_status),
data_hora
FROM tb_status
WHERE (data_hora BETWEEN '2019-08-15 16:00:00' AND '2019-08-16 17:00:00')
GROUP BY dataformat,
intervals,
eq_id
ORDER BY 3,2,1;
That one query returns a good part of what I need, however, it does not return times when it has no data, for example:
2019-08-15 16:15:00 ...
2019-08-15 16:30:00 ...
2019-08-15 16:45:00 ...
2019-08-15 17:00:00 ...
2019-08-15 17:30:00 ... <------ Pulou um intervalo
2019-08-15 16:45:00 ...
See in the manual the Common Table Expressions with the WITH clause (with the desired time interval) and use the result in a LEFT OUTER JOIN with your table.
– anonimo
I will check! Thanks for the comment, now I have to start again!
– Leandro K.
see this other question, it should help is a very similar case: https://answall.com/a/398850/57220
– Ricardo Pontual
Thank you Ricardo! I will base myself on this question
– Leandro K.