finding the minimal date_time within a window of time using sql
December 19, 2021Im trying to model some data using sql, the column i would like to generate is date_started – all others is given.
date_started = the minimal date_created with the same id1 and id2 in range of 2 hours that not belong to any other group of rows.
for example, for date_created = 2021-11-02 05:23:41.769, date_started = 2021-11-02 05:23:41.769 itself.
because 2021-11-02 04:10:39.823 is in range of two hours but belong to 2021-11-02 02:16:28.544 group already.
| id1 | id2 | date_created | date_started |
|---|---|---|---|
| 1 | 2 | 2021-11-02 02:16:28.544 | 2021-11-02 02:16:28.544 |
| 1 | 2 | 2021-11-02 02:52:52.504 | 2021-11-02 02:16:28.544 |
| 1 | 2 | 2021-11-02 04:10:39.823 | 2021-11-02 02:16:28.544 |
| 1 | 2 | 2021-11-02 05:23:41.769 | 2021-11-02 05:23:41.769 |
| 1 | 2 | 2021-11-02 06:33:11.564 | 2021-11-02 05:23:41.769 |
| 1 | 2 | 2021-11-02 08:30:14.564 | 2021-11-02 08:30:14.564 |
