How to detect overlapping time ranges in PostgreSQL

Question

I have a table like this

CREATE TABLE public.userlocation
(
  datetime timestamp with time zone,
  location geometry,
  locationtype integer,
  buffer double precision,
  timebuffer double precision,
  "userID" numeric,
  "ID" integer NOT NULL DEFAULT nextval('"userlocation_ID_seq"'::regclass),
  "time" time with time zone
)

each row has a time and then a value to make a dynamic time range, in fact If time=8 and timebuffer=15so time range would be endTime= 8+15min and startTime= 8-15 min.I can do this simply using this query

select f1.*,f1.time +(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time-(f1.timebuffer::text||' minute')::INTERVAL as endTime

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

PostgreSQL query to detect overlapping time ranges

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

    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, '()') && tsrange(f1.startTime, f1.endTime, '()')
                and f2.locationtype = f1.locationtype
                and f2.locationtype=1
                and f2."ID" <> f1."ID");

But I get this error

[2016-08-27 23:42:45] [42703] ERROR: column f2.starttime does not exist

Position: 372

I think At first I should create F2 table but I dont know how,Can you please give me some hints?

the alias `as startTime` is not visible from the inner query. Push it into a subquery and join with that. (or use a CTE) ( a temp view could also work) — wildplasser, Aug 27 '16 at 19:26
Why is startTime > endTime in your query? I mean the operation you are doing with adding/substracting interval. — Kamil Gosciminski, Aug 27 '16 at 19:26
@wildplasser sorry but I cant figure out what you mean,Can you please give me some example?thanks — Majid Hojati, Aug 27 '16 at 19:32
BTW: the `f1.startTime` is also not visible; but `f2.startTime` is seen first by the parser. — wildplasser, Aug 27 '16 at 19:35

wildplasser · Accepted Answer · 2016-08-27T20:06:38.917

First: The column-alias (select expression AS somename) is not usable from within its query, it is only visible from outside the query. You can solve this by wrapping it into a (subquery) xx or a view or a CTE
Second: don't repeat yourself: if you need to compute the same expression(s) twice, you could be doing too much ...

CREATE TEMP VIEW omg AS
        SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime
        ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime
        , fx.locationtype
        , fx.ID
        -- ... maybe more columns and expressions ...
        FROM userlocation fx
        ;


SELECT f1.startTime, f1.endTime
        -- ... maybe more columns and expressions ...
FROM omg f1 
WHERE EXISTS (
   SELECT 1
   FROM omg f2
   WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()')
   AND f2.locationtype = f1.locationtype
   AND f2.locationtype=1
   AND f2."ID" <> f1."ID")
        ;

instead of a view you could use a CTE (the view probably performs better)
you could probably pull the tsrange into the view or CTE, too
I didn't check the logic

For completeness, the CTE version (which almost looks the same)

WITH omg AS (
        SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime
        ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime
        , fx.locationtype
        , fx.ID
        -- ... maybe more columns and expressions ...
        FROM userlocation fx
        )
SELECT f1.startTime, f1.endTime
        -- ... maybe more columns and expressions ...
FROM omg f1 
WHERE EXISTS (
   SELECT 1
   FROM omg f2
   WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()')
   AND f2.locationtype = f1.locationtype
   AND f2.locationtype=1
   AND f2."ID" <> f1."ID")
        ;

Thanks very much, But I get this error ERROR: column "locationtype" specified more than once what is wrong? — Majid Hojati, Aug 27 '16 at 19:51
You added `, locationtype` where I had put the `...` You need to prefix it with either `f1.` or `f2.` , (but they are the same anyway) — wildplasser, Aug 27 '16 at 19:53
No,In fact I've just copied your query and changed fx.ID into fx."ID", I havent changed anything else — Majid Hojati, Aug 27 '16 at 19:56
Wow..I get my mistake..As you said I have to delete colomns from ... part,But now I get this error,about casting. what I should do?function tsrange(time with time zone, time with time zone, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. — Majid Hojati, Aug 27 '16 at 19:58

How to detect overlapping time ranges in PostgreSQL

1 Answers1