Site icon

finding the minimal date_time within a window of time using sql

Im 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
Exit mobile version