title: Creating a PostgreSQL `tsrange` from two timestamps
body: <p>I am trying to create a <code>tsrange</code> (last Thursday to the previous Thursday) in a postgresql query but I get cast errors.</p>
  
  <p>This is what I have got so far (starting off from <a href="https://stackoverflow.com/a/30784305/222529">this SO question</a>).</p>
  
  <pre><code>WITH past_week AS (
      SELECT date_trunc('day', NOW() + (s::TEXT || ' day')::INTERVAL)::TIMESTAMP(0) AS day 
      FROM generate_series(-7, 0, 1) AS s)
  SELECT (
  date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4') - '7 day'::INTERVAL),
  date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4')));
  </code></pre>
  
  <p>And this is the result (correct value, but not format, since it's not a range):</p>
  
  <pre><code>                      row                      
  -----------------------------------------------
   ("2015-10-29 00:00:00","2015-11-05 00:00:00")
  (1 row)
  </code></pre>
  
  <p>Now, there are 2 main things that bug me:</p>
  
  <ol>
  <li><p>If I try and add a <code>::tsrange</code> right before the end of the query, the interpreter complains that:</p>
  
  <p>ERROR:  cannot cast type record to tsrange
  LINE 6: ...ROM past_week WHERE EXTRACT(DOW FROM day) = '4')))::tsrange;</p></li>
  <li><p>I would love to avoid repetition, but I'm not that proficient in SQL to know how. Any improvement is more than welcome.</p></li>
  </ol>