How to return time in Mysql even without registration at that time

Asked

Viewed 30 times

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.

  • I will check! Thanks for the comment, now I have to start again!

  • see this other question, it should help is a very similar case: https://answall.com/a/398850/57220

  • Thank you Ricardo! I will base myself on this question

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.