SQL Server 2017

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

DECLARE @t TABLE (timestamp DATETIME, mode INT);
INSERT INTO @t VALUES
('2018-01-01 12:00', 1),
('2018-01-01 18:00', 2),
('2018-01-02 01:00', 1),
('2018-01-02 02:00', 2),
('2018-01-04 04:00', 1);

WITH cte1 AS (
    -- the min and max dates in your data
    SELECT
        CAST(MIN(timestamp) AS DATE) AS mindate,
        CAST(MAX(timestamp) AS DATE) AS maxdate
    FROM @t
), cte2 AS (
    -- build all dates between min and max dates using recursive cte
    SELECT mindate AS day_start, DATEADD(DAY, 1, mindate) AS day_end, maxdate
    FROM cte1
    UNION ALL
    SELECT DATEADD(DAY, 1, day_start), DATEADD(DAY, 2, day_start), maxdate
    FROM cte2
    WHERE day_start < maxdate
), cte3 AS (
    -- pull end datetime from next row into current
    SELECT
        timestamp AS dt_start,
        LEAD(timestamp) OVER (ORDER BY timestamp) AS dt_end,
        mode
    FROM @t
), cte4 AS (
    -- join datetime with date using date overlap query
    -- then clamp start datetime to 00:00 of the date
    -- and clamp end datetime to 00:00 of next date
    SELECT 
        IIF(dt_start < day_start, day_start, dt_start) AS dt_start_fix, 
        IIF(dt_end > day_end, day_end, dt_end) AS dt_end_fix,
        mode
    FROM cte2
    INNER JOIN cte3 ON day_end > dt_start AND dt_end > day_start
)
SELECT dt_start_fix, dt_end_fix, mode, datediff(hour, dt_start_fix, dt_end_fix)
FROM cte4

dt_start_fix	dt_end_fix	mode	(No column name)
01/01/2018 12:00:00	01/01/2018 18:00:00	1	6
01/01/2018 18:00:00	02/01/2018 00:00:00	2	6
02/01/2018 00:00:00	02/01/2018 01:00:00	2	1
02/01/2018 01:00:00	02/01/2018 02:00:00	1	1
02/01/2018 02:00:00	03/01/2018 00:00:00	2	22
03/01/2018 00:00:00	04/01/2018 00:00:00	2	24
04/01/2018 00:00:00	04/01/2018 04:00:00	2	4