'Exclude Sub Intervals from parent interval in postgresql
I have 2 tables:
Table 1 has Users Session Data
| user_id | login_time | logout_time |
|---|---|---|
| a1 | 2022-01-10 09:02:54.927 | 2022-01-10 18:07:42.876 |
| s1 | 2022-01-10 09:07:51.104 | 2022-01-10 18:44:23.053 |
Table 2 has Users Chat Data
| user_id | user_connected_time | user_disconnected_time |
|---|---|---|
| a1 | 2022-02-10 13:10:49.975 | 2022-02-10 13:25:22.26 |
| a1 | 2022-02-10 17:35:12.34 | 2022-02-10 17:55:05.283 |
| s1 | 2022-02-15 14:08:34.39 | 2022-02-15 14:09:28.627 |
| s1 | 2022-02-15 14:00:47.261 | 2022-02-15 14:16:29.339 |
I need the agent ideal time i.e. when the agent wasn't connected on any chat.
Expected output
Agent Ideal Duration
| agent_ideal_duration | agent_id |
|---|---|
| 08:44:55.006 | a1 |
| 09:20:49.871 | s1 |
Overlapping chat duration is removed from Agent's s1 ideal time.
Note: 1 agent can take multiple chats at a time.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|