Report with date_trunc including zeroed values

Asked

Viewed 39 times

1

I’m doing a consultation in postgres, but it only brings the results where the inspection body is not reset, I needed this query to bring all the results from 8 to 18, including zeroes:

AS YOU BRING:

inserir a descrição da imagem aqui

HOW YOU WANTED ME TO BRING:

inserir a descrição da imagem aqui

QUERY:

select (
          date_trunc('hour', data)

           )                 intervalo_data,
       count(id) fiscalizacoes
from fiscalizacoes
where data >= '2019-06-21 08:00:0.000000'
  and data <= '2019-06-21 18:00:00.000000'
group by intervalo_data
order by intervalo_data;
sql postgresql

  • are using date_trunc in select but grouping with the normal date? it doesn’t look like it will work as expected, you have to let the group by equal to select

  • The grouping is working, I executed and returned what I expected, even because I have to bring the number of inspections per 1 hour interval, from 8 to 18.

  • It’s not that it’s not working, but it groups for the whole date without truncating hours. There are all these hours that is not returning, for example 11:00:00?

  • Grouped by date_trunc('hour', date) and continues with the same result, not ignoring the results

  • but if not group, there is record in the hours that are missing?

  • does not exist, is this my question, if I have to return 0 for the hours that have no instance.

  • ah now I understand :)

Show 2 more comments

1 answer

1


Yes it is possible to generate all dates using the function generate_series:

generate_series(timestamp '2019-06-21 00:00:0.00000', '2019-06-21 23:00:0.00000', '1 hour')

Basically generates a series of dates(timestamp, but it could be int, and generate a range of integer numbers for example), has the beginning and end, and at the end the desired interval, which is in hours (every 1 hour).

From there, just make a LEFT JOIN with the table (LEFT because we want to preserve the dates that there are not in the table "fiscalizacoes") and group normally:

SELECT  date_trunc('hour', datas.data) intervalo_data,
 count(f.id) fiscalizacoes
  FROM (
       SELECT day::timestamp as data
         FROM generate_series(timestamp '2019-06-21 00:00:0.00000', '2019-06-21 23:00:0.00000', '1 hour') day
       ) datas
LEFT JOIN fiscalizacoes f ON f.data = datas.data
GROUP BY intervalo_data
ORDER BY intervalo_data;

I put an example working here: SQL Fiddle

  • thanks a lot, it worked.

  • 1

    I also thank you, I had seen this function for whole numbers and did not know if it generated dates tb, then I researched and assembled this answer, I ended up learning something new :)

Browser other questions tagged sql postgresql

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