CODEWITHSUNDEEP
postgresqlperiod
fadedbee
fadedbee

Reputation: 44745

How to do logical operations on time periods in Postgres?

I have some time periods:

Mobile Event Time
  A    START 13:00
  B    START 13:05
  A    STOP  14:00
  B    STOP  14:05
  A    START 15:00
  A    STOP  16:00

How can I do logical operations on such data, such that I can get:

Mobile Event Time
A | B  START 13:00
A | B  STOP  14:05
A | B  START 15:00
A | B  STOP  16:00

and

Mobile Event Time
A & B  START 13:05
A & B  STOP  14:00

Upvotes: 0

Views: 61

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21336

If I understand your requirements correctly, then "logical operations" is probably not the best way to describe these; they're really set intersection and union operations (though the two are strongly related).

Postgres' range operators can calculate unions and intersections, so it might help to start by pairing up the start/end timestamps (as in your previous question) to build tsrange values.

Once that's in place, finding the intersection (A & B) is relatively straightforward: 

WITH time_pair AS (
  SELECT *, lead("time") OVER (PARTITION BY mobile ORDER BY "time") AS next_time
  FROM events
  WHERE event IN ('START', 'STOP')
),
time_range AS (
  SELECT mobile, tsrange("time", next_time) AS period
  FROM time_pair
  WHERE event = 'START'
)
SELECT 'A & B', a_range.period * b_range.period
FROM time_range a_range
JOIN time_range b_range ON
  a_range.period && b_range.period
WHERE
  a_range.mobile = 'A' AND
  b_range.mobile = 'B';

The union (A | B) is a bit more involved; if a range overlaps on both ends, then there are (at least) three ranges contributing to every output row, so a single JOIN won't be enough.

On the other hand, this problem is a bit more general, as you can just merge any overlapping ranges together with no regard for exactly which user they came from, so it's a bit easier to find existing implementations. This answer seems fairly comprehensive. You can find a few more by searching for "postgres range aggregation".

Upvotes: 1

Related Questions