title: Using age in postgresql
body: <p>I have a table in my postgresql 8.4 database like this:</p>
  
  <pre><code>id(serial), event_type_id(id, foreign key), ts(timestamp)
  </code></pre>
  
  <p>I have two event type id's that I need to see the age between. </p>
  
  <pre><code>Event_type_id 1 = Arrive
  Event_type_id 2 = Leaves
  </code></pre>
  
  <p>example data</p>
  
  <pre><code> id | event_type_id |             ts             
  ----+---------------+----------------------------
   21 |             6 | 2012-04-03 16:02:18.739274
   20 |             5 | 2012-04-03 08:44:13.244287
  </code></pre>
  
  <p>I want to group these by day and see how many hours per day it is. </p>
  
  <p>can someone point me in the direction how to group and calculate the age?</p>
  
  <p>So far I have this, but I need to generate a series to join on or something similar</p>
  
  <p><strong>Solution</strong>
  Don't know if it's best practice but it works for me. </p>
  
  <pre><code>SELECT dx, age(max(ts), min(ts)) 
  from generate_series('2012-04-01', '2012-04-14', interval '1 day') as dx 
  left join events on (ts &gt;= dx and ts &lt; dx + '23 hours 59 minutes' and event_type_id in (5,6)) 
  group by dx 
  order by dx;
  </code></pre>