title: How to detect overlapping time ranges in PostgreSQL
body: <p>I have a table like this</p>
  <pre><code>CREATE TABLE public.userlocation
  (
    datetime timestamp with time zone,
    location geometry,
    locationtype integer,
    buffer double precision,
    timebuffer double precision,
    &quot;userID&quot; numeric,
    &quot;ID&quot; integer NOT NULL DEFAULT nextval('&quot;userlocation_ID_seq&quot;'::regclass),
    &quot;time&quot; time with time zone
  )
  </code></pre>
  <p>each row has a time and then a value to make a dynamic time range, in fact If <code>time=8</code> and <code>timebuffer=15</code>so time range would be <code>endTime= 8+15min</code> and <code>startTime= 8-15 min</code>.I can do this simply using this query</p>
  <pre><code>select f1.*,f1.time +(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time-(f1.timebuffer::text||' minute')::INTERVAL as endTime
  </code></pre>
  <p>everything work fine, After this stage I want to query rows that their time period is between startTime and endTime in other word they overlap. I have already found this question</p>
  <p><a href="https://stackoverflow.com/questions/25733385/postgresql-query-to-detect-overlapping-time-ranges">PostgreSQL query to detect overlapping time ranges</a></p>
  <p>But there is a difference here,I don't have start time and endtime so I have to create them using above method. so hare is my query</p>
  <pre><code>    select f1.*,f1.time -(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time+(f1.timebuffer::text||' minute')::INTERVAL as endTime
  from userlocation f1
  where exists (select f2.time -(f2.timebuffer::text||' minute')::INTERVAL as startTime,f2.time+(f2.timebuffer::text||' minute')::INTERVAL as endTime
                from userlocation f2
                where tsrange(f2.startTime, f2.endTime, '()') &amp;&amp; tsrange(f1.startTime, f1.endTime, '()')
                  and f2.locationtype = f1.locationtype
                  and f2.locationtype=1
                  and f2.&quot;ID&quot; &lt;&gt; f1.&quot;ID&quot;);
  </code></pre>
  <p>But I get this error</p>
  <pre><code>[2016-08-27 23:42:45] [42703] ERROR: column f2.starttime does not exist
  </code></pre>
  <p>Position: 372</p>
  <p>I think At first I should create F2 table but I dont know how,Can you please give me some hints?</p>