title: Return count of rows on today's date grouped by the hour w/ 0's for hours with no count
body: <p>I am trying to get the count of rows within a hour, starting with 12 am to the current hour. I currently am getting the last 24 hours easily, but am unsure how start the series with 2/17/2020 12:00 am up to the current time.</p>
  
  <p>Here is what I have currently returning the last 24 hours where hours with no count return 0:</p>
  
  <pre><code>with hour_range as (
      select generate_series(date_trunc('hour', now() - interval '23 hours'), date_trunc('hour', now()), '1 hour':: interval) as hour
  ),
  
  hour_count as (
      select date_trunc('hour', local_start_time) as hour,
          count (*) as ct
      from call_data.rc_api_call_logs
      where local_start_time::date = current_date -- today
      and br_call_flag = 1
      group by 1
  )
  
  select to_char(hour_range.hour, 'Mon DD YYYY HH12:MI:SSam'),
         coalesce(hour_count.ct, 0)
  from hour_range
  left outer join hour_count on hour_range.hour = hour_count.hour;
  </code></pre>
  
  <p>Also I am just reformatting military time to normal time in my last select for readability. The local_start_time is of <strong>timestamp</strong> type. The issue is really with the generate_series query, and fixing the start time since I am using it to join to get the hours with count 0.</p>
  
  <p><strong>EDIT:</strong> Any suggestions for performance will be helpful, otherwise I will accept my answer.</p>