title: Comparing counts of two date ranges in Postgres
body: <p>I am trying to compare two date ranges from a single PostgreSQL 9.6.5 table.  The two date ranges are the same length as each other and are in the style of <em>this week</em> versus <em>last week</em>, <em>this month</em> versus <em>last month</em>, <em>last 180 days</em> versus <em>previous 180 days</em>.  The table captures customer interactions, each row recording a single interaction - date, start_time, stop_time, location, etc.</p>
  
  <p>I have managed to construct the SQL for a single date range.</p>
  
  <pre><code>SELECT 
    to_char(date, 'day') as day,
    extract(dow from date) as dow,
    count(*) AS curr_count
  FROM schema.table
  WHERE (date between :date_start AND :date_stop) 
  GROUP BY day, dow
  ORDER BY dow ASC
  </code></pre>
  
  <p>With the <strong>start_date</strong> set to '2018-08-08' and the <strong>stop_date</strong> set to '2018-08-15', I get this rescordset:</p>
  
  <pre><code>Array ( 
   [0] =&gt; Array ( [day] =&gt; monday [0] =&gt; monday [dow] =&gt; 1 [1] =&gt; 1 [curr_count] =&gt; 78 [2] =&gt; 78 ) 
   [1] =&gt; Array ( [day] =&gt; tuesday [0] =&gt; tuesday [dow] =&gt; 2 [1] =&gt; 2 [curr_count] =&gt; 75 [2] =&gt; 75 ) 
   [2] =&gt; Array ( [day] =&gt; wednesday [0] =&gt; wednesday [dow] =&gt; 3 [1] =&gt; 3 [curr_count] =&gt; 62 [2] =&gt; 62 ) 
   [3] =&gt; Array ( [day] =&gt; thursday [0] =&gt; thursday [dow] =&gt; 4 [1] =&gt; 4 [curr_count] =&gt; 68 [2] =&gt; 68 ) 
   [4] =&gt; Array ( [day] =&gt; friday [0] =&gt; friday [dow] =&gt; 5 [1] =&gt; 5 [curr_count] =&gt; 81 [2] =&gt; 81 ) 
   [5] =&gt; Array ( [day] =&gt; saturday [0] =&gt; saturday [dow] =&gt; 6 [1] =&gt; 6 [curr_count] =&gt; 3 [2] =&gt; 3 ) 
  )
  </code></pre>
  
  <p>I can calculate the dates for the previous period (2018-08-01 to 2018-08-07) easily enough but I don't understand how to add these into the SQL to get the desired result.  The end result I would like is:</p>
  
  <pre><code>array (
   array (day, dow, curr_count, prev_count)
   array (day, dow, curr_count, prev_count)
   array (day, dow, curr_count, prev_count)
   array (day, dow, curr_count, prev_count)
  )
  </code></pre>
  
  <p>Any help is greatly appreciated.</p>