How to split a date column into two columns

Question

Please tell me, how to split a date column into two columns:

Column
-------------------
28.10.2022 00:25:13
02.11.2022 10:20:23
08.11.2022 08:25:26
29.11.2022 09:50:21
02.12.2022 01:01:13
27.12.2022 22:30:02

Need to do this:

Column1             | Column2
--------------------+--------------------
28.10.2022 00:25:13 | 02.11.2022 10:20:23
03.11.2022 10:20:23 | 08.11.2022 08:25:26
09.11.2022 08:25:26 | 29.11.2022 09:50:21
30.11.2022 09:50:21 | 02.12.2022 01:01:13
03.12.2022 01:01:13 | 27.12.2022 22:30:02

The table_date table stores the ID of the operation, the date, and the value "BUCKET" which can change on the date

WITH groups as 
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE) AS rn,
        (DATE - (ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE))) AS grp,
        DATE,
        d.BUCKET, 
        d.id
    FROM 
        table_date d
    WHERE 
        d.id = '123'
)
SELECT
    MIN(g.DATE) AS DATE_IN,
    MAX(g.DATE) AS DATA_OUT, 
    g.id, 
    g.BUCKET
FROM 
    groups g
GROUP BY 
    g.grp, g.id, g.GROUP_ID, g.BUCKET_ID
ORDER BY 
    MIN(g.DATE)

Answer 1

This is a solution completely different than yours, I resolved it by using ROW_NUMBER() to assign a unique sequential number, then I used GROUP BY to organize the rows into groups that are expected to represent successive records based on the rn column :

with cte as (
  select dt, row_number() over (order by dt) as rn
  from table_date
),
grp1 as (
  select min(dt) Column1, max(dt) Column2 
  from cte
  group by round(rn/2)
  having min(dt) <> max(dt)
),
grp2 as (
  select min(dt) Column1, max(dt) Column2 
  from cte
  group by round(rn/2 -0.1)
  having min(dt) <> max(dt)
)
select *
from (
  select * from grp1
  union all 
  select * from grp2
) s
order by COLUMN1

Results :

COLUMN1              COLUMN2
28.10.2022 00:25:13  02.11.2022 10:20:23
02.11.2022 10:20:23  08.11.2022 08:25:26
08.11.2022 08:25:26  29.11.2022 09:50:21
29.11.2022 09:50:21  02.12.2022 01:01:13
02.12.2022 01:01:13  27.12.2022 22:30:02

Demo here