I have a query like the following that takes a min_date and max_date.
Select TO_CHAR(date_trunc('hour', table.date), 'YYYY-MM-DD HH24:00') as formatted_date,
count(DISTINCT table.order_id) as unique_orders, sum(table.price) as total_price from table
where table.date>=min_date and table.date<=max_date
result = ActiveRecord::Base.connection.execute(query).values.to_a
This will give me a count of the orders and the total price per "hour". For example, say this is my table
<id: 1, order_id: 1, date: 2020-06-10 10:18:01+00, price:10>
<id: 2, order_id: 1, date: 2020-06-10 10:43:19+00, price:14>
<id: 3, order_id: 2, date: 2020-06-10 12:10:00+00, price:15>
<id: 4, order_id: 3, date: 2020-06-10 12:20:21+00, price:10>
<id: 5, order_id: 3, date: 2020-06-10 14:10:54+00, price:20>
And my min_date = 2020-06-10 09:00:00+00 and max_date = 2020-06-10 14:00:00+00. This would be my result:
[[2020-06-10 10:00, 1, 24], [2020-06-10 12:00, 2, 25], [2020-06-10 14:00, 1, 20]]
I want the missing hours in between the min_date and max_date to be included like so:
[[2020-06-10 09:00, 0, 0], [2020-06-10 10:00, 1, 24], [2020-06-10 11:00, 0, 0], [2020-06-10 12:00, 2, 25], [2020-06-10 13:00, 0, 0] [2020-06-10 14:00, 1, 20]]
So I want also all of the hours that are not present in the table but that is included in the time range between min_date and max_date. In this scenario, it's all the hours I want but it might be all the 'weeks' or 'months' depending on the argument I pass to date_trunc.
Use generate_series() to calculate all the dates you want. Then use left join to bring in the data:
select to_char(gs.dte, 'YYYY-MM-DD HH24:00') as formatted_date,
count(distinct t.order_id) as unique_orders, sum(t.price) as total_price
from generate_series(min_date, max_date, interval '1 hour') gs(dte) left join
table t
on t.date >= gs.dte and
t.date < gs.dte + interval '1 hour' and table.date<=max_date
group by gs.dte
order by gs.dte;