Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TEMP TABLE tbl (period_id uuid, created_at timestamptz);
INSERT INTO tbl VALUES
  (md5('A')::uuid, '2017-04-11 11:13:47.997+00')
, (md5('A')::uuid, '2017-04-11 14:42:51.843+00')
, (md5('B')::uuid, '2017-05-21 15:18:12.973+00')
, (md5('B')::uuid, '2017-05-21 15:28:41.054+00')
, (md5('B')::uuid, '2017-05-21 15:28:57.577+00')
, (md5('C')::uuid, '2017-06-11 22:48:00.637+00')
, (md5('D')::uuid, '2017-07-15 14:45:52.343+00')
, (md5('D')::uuid, '2017-07-15 14:47:53.343+00')
, (md5('E')::uuid, '2017-08-25 16:22:57.612+00')
, (md5('F')::uuid, '2017-09-16 00:10:18.577+00')
;
10 rows affected

SELECT period_id, created_at, md5(period_id::text || grp)::uuid AS period_id2
FROM  (
   SELECT *, count(*) FILTER (WHERE step) OVER (PARTITION BY period_id ORDER BY created_at) AS grp
   FROM  (
      SELECT *, (created_at - lag(created_at) OVER (PARTITION BY period_id ORDER BY created_at)) > interval '10 min' AS step
      FROM  tbl
      ) sub1
   ) sub2;


period_id
created_at
period_id2


0d61f837-0cad-1d41-2f80-b84d143e1257
2017-06-11 23:48:00.637+01
cd1005e7-2040-a195-3dee-98f59d250f19

3a3ea00c-fc35-332c-edf6-e5e9a32e94da
2017-08-25 17:22:57.612+01
04723af3-a8f7-6a69-8848-e209f7a2c47e

7fc56270-e7a7-0fa8-1a59-35b72eacbe29
2017-04-11 12:13:47.997+01
a4d67b8d-8796-9acc-011e-f47ab1292b60

7fc56270-e7a7-0fa8-1a59-35b72eacbe29
2017-04-11 15:42:51.843+01
bceadfbf-2ef8-b988-c3c1-29a6ee93d917

80061894-3025-315f-869e-4e1f09471012
2017-09-16 01:10:18.577+01
a04208ff-732c-8b0a-a882-90b61f43861c

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:18:12.973+01
068f1385-cd43-fdf3-cfc8-84cf71a42665

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:28:41.054+01
24cb251d-a814-d5f6-345f-663ea78807b8

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:28:57.577+01
24cb251d-a814-d5f6-345f-663ea78807b8

f623e75a-f30e-62bb-d73d-6df5b50bb7b5
2017-07-15 15:45:52.343+01
55d26339-6fd6-4887-0ef6-6466908cac8d

f623e75a-f30e-62bb-d73d-6df5b50bb7b5
2017-07-15 15:47:53.343+01
55d26339-6fd6-4887-0ef6-6466908cac8d


SELECT period_id, created_at
     , md5(period_id::text || count(*) FILTER (WHERE step) OVER (PARTITION BY period_id ORDER BY created_at))::uuid AS period_id2
FROM  (
   SELECT *, (created_at - lag(created_at) OVER (PARTITION BY period_id ORDER BY created_at)) > interval '10 min' AS step
   FROM  tbl
   ) sub;


period_id
created_at
period_id2


0d61f837-0cad-1d41-2f80-b84d143e1257
2017-06-11 23:48:00.637+01
cd1005e7-2040-a195-3dee-98f59d250f19

3a3ea00c-fc35-332c-edf6-e5e9a32e94da
2017-08-25 17:22:57.612+01
04723af3-a8f7-6a69-8848-e209f7a2c47e

7fc56270-e7a7-0fa8-1a59-35b72eacbe29
2017-04-11 12:13:47.997+01
a4d67b8d-8796-9acc-011e-f47ab1292b60

7fc56270-e7a7-0fa8-1a59-35b72eacbe29
2017-04-11 15:42:51.843+01
bceadfbf-2ef8-b988-c3c1-29a6ee93d917

80061894-3025-315f-869e-4e1f09471012
2017-09-16 01:10:18.577+01
a04208ff-732c-8b0a-a882-90b61f43861c

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:18:12.973+01
068f1385-cd43-fdf3-cfc8-84cf71a42665

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:28:41.054+01
24cb251d-a814-d5f6-345f-663ea78807b8

9d5ed678-fe57-bcca-6101-40957afab571
2017-05-21 16:28:57.577+01
24cb251d-a814-d5f6-345f-663ea78807b8

f623e75a-f30e-62bb-d73d-6df5b50bb7b5
2017-07-15 15:45:52.343+01
55d26339-6fd6-4887-0ef6-6466908cac8d

f623e75a-f30e-62bb-d73d-6df5b50bb7b5
2017-07-15 15:47:53.343+01
55d26339-6fd6-4887-0ef6-6466908cac8d

period_id	created_at	period_id2
0d61f837-0cad-1d41-2f80-b84d143e1257	2017-06-11 23:48:00.637+01	cd1005e7-2040-a195-3dee-98f59d250f19
3a3ea00c-fc35-332c-edf6-e5e9a32e94da	2017-08-25 17:22:57.612+01	04723af3-a8f7-6a69-8848-e209f7a2c47e
7fc56270-e7a7-0fa8-1a59-35b72eacbe29	2017-04-11 12:13:47.997+01	a4d67b8d-8796-9acc-011e-f47ab1292b60
7fc56270-e7a7-0fa8-1a59-35b72eacbe29	2017-04-11 15:42:51.843+01	bceadfbf-2ef8-b988-c3c1-29a6ee93d917
80061894-3025-315f-869e-4e1f09471012	2017-09-16 01:10:18.577+01	a04208ff-732c-8b0a-a882-90b61f43861c
9d5ed678-fe57-bcca-6101-40957afab571	2017-05-21 16:18:12.973+01	068f1385-cd43-fdf3-cfc8-84cf71a42665
9d5ed678-fe57-bcca-6101-40957afab571	2017-05-21 16:28:41.054+01	24cb251d-a814-d5f6-345f-663ea78807b8
9d5ed678-fe57-bcca-6101-40957afab571	2017-05-21 16:28:57.577+01	24cb251d-a814-d5f6-345f-663ea78807b8
f623e75a-f30e-62bb-d73d-6df5b50bb7b5	2017-07-15 15:45:52.343+01	55d26339-6fd6-4887-0ef6-6466908cac8d
f623e75a-f30e-62bb-d73d-6df5b50bb7b5	2017-07-15 15:47:53.343+01	55d26339-6fd6-4887-0ef6-6466908cac8d