PostgreSQL build working range from one date column

Question

I'm using PostgreSQL v. 11.2

I have a table

|id | bot_id |        date        |
| 1 |  1     | 2020-04-20 16:00:00| 
| 2 |  2     | 2020-04-22 12:00:00| 
| 3 |  3     | 2020-04-24 04:00:00| 
| 4 |  1     | 2020-04-27 09:00:00| 

And for example, I have DateTime range 2020-03-30 00:00:00 and 2020-04-30 00:00:00

I need to show get working ranges to count the total working hours of each bot.

Like this:

|bot_id | start_date          |       end_date       |
| 1     | 2020-03-30 00:00:00 |  2020-04-20 16:00:00 |
| 2     | 2020-04-20 16:00:00 |  2020-04-22 12:00:00 |
| 3     | 2020-04-22 12:00:00 |  2020-04-24 04:00:00 |
| 1     | 2020-04-24 04:00:00 |  2020-04-27 09:00:00 |
| 1     | 2020-04-27 09:00:00 |  2020-04-30 00:00:00 |

I've tried to use LAG(date) but I'm not getting first and last dates of the range.

Answer 1

You could use a UNION ALL, with one part building the start_date/end_date couples from your values & the other part filling in the last period (from the last date to 2020-04-30 00:00:00):

WITH values (id, bot_id, date) AS (
    VALUES (1, 1, '2020-04-20 16:00:00'::TIMESTAMP)
         , (2, 2, '2020-04-22 12:00:00')
         , (3, 3, '2020-04-24 04:00:00')
         , (4, 1, '2020-04-27 09:00:00')
)
(
    SELECT bot_id
         , LAG(date, 1, '2020-03-30 00:00:00') OVER (ORDER BY id) AS start_date
         , date                                                   AS end_date
    FROM values
)
UNION ALL
(
    SELECT bot_id
         , date                  AS start_date
         , '2020-04-30 00:00:00' AS end_date
    FROM values
    ORDER BY id DESC
    LIMIT 1
)

---

+------+--------------------------+--------------------------+
|bot_id|start_date                |end_date                  |
+------+--------------------------+--------------------------+
|1     |2020-03-30 00:00:00.000000|2020-04-20 16:00:00.000000|
|2     |2020-04-20 16:00:00.000000|2020-04-22 12:00:00.000000|
|3     |2020-04-22 12:00:00.000000|2020-04-24 04:00:00.000000|
|1     |2020-04-24 04:00:00.000000|2020-04-27 09:00:00.000000|
|1     |2020-04-27 09:00:00.000000|2020-04-30 00:00:00.000000|
+------+--------------------------+--------------------------+