how to split a date column into two columns:

December 4, 2023

Good afternoon!
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)

>Solution :

Assuming your date column is called DATE, you can achieve this using the LEAD window function in combination with the PARTITION BY clause. Here’s an example of how you can modify your query to achieve this:

WITH groups AS (
      SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE) AS rn,
        LEAD(DATE) OVER (PARTITION BY id ORDER BY DATE) AS next_date,
        DATE,
        d.BUCKET, 
        d.id
      FROM table_date d
      WHERE d.id = '123'
    )
      
    SELECT
      g.DATE AS DATE_IN,
      g.next_date AS DATE_OUT, 
      g.id, 
      g.BUCKET
    FROM groups g
    WHERE g.next_date IS NOT NULL
    ORDER BY g.DATE;