Comment couper une ligne à l'heure de début et à l'heure de fin en plusieurs lignes d'heures passées

même s'il y aura sûrement des solutions plus élégantes. Cela donnera le résultat souhaité:

 TIMES                 DIFF
 03/05/2019 08:00:00   16
 04/05/2019            24
 05/05/2019 12:00:00   12

Résultat:

WITH cte ( starttime ) AS (
     SELECT
         CAST(TO_DATE('03.05.2019:08:00:00','dd.mm.yyyy:hh24:mi:ss') AS DATE) cte_date
     FROM dual
     UNION ALL
     SELECT CAST( (starttime + 1) AS DATE) starttime
     FROM cte
     WHERE trunc(starttime) + 1 <= TO_DATE('05.05.2019:12:00:00','dd.mm.yyyy:hh24:mi:ss')
 ),starttime AS (
     SELECT
         TO_DATE('03.05.2019:08:00:00','dd.mm.yyyy:hh24:mi:ss') AS starttime
     FROM dual
 ),endtime AS (
     SELECT
         TO_DATE('05.05.2019:12:00:00','dd.mm.yyyy:hh24:mi:ss') AS endtime
     FROM dual
 )
 SELECT
     a.*,
     CASE 
        WHEN TO_CHAR( (trunc(times + 1) ),'hh24') - TO_CHAR(times,'hh24') = 0 THEN -24 *-1
        ELSE ( 24 - ( TO_CHAR( (trunc(times + 1) ),'hh24') - TO_CHAR(times,'hh24') ) *-1 )
     END diff
 FROM(
     SELECT
         CASE 
            WHEN TO_CHAR(starttime,'yyyy-mm-dd') = (SELECT TO_CHAR(endtime,'yyyy-mm-dd') FROM endtime) THEN 
                (  SELECT endtime FROM endtime )
            WHEN TO_CHAR(starttime,'yyyy-mm-dd') = (SELECT TO_CHAR(starttime,'yyyy-mm-dd') FROM starttime) THEN 
                (  SELECT starttime FROM starttime )
            ELSE trunc(starttime)
         END times
     FROM cte a
 ) a

Vous pouvez utiliser la syntaxe connect by level combinée avec des instructions case..when

select trunc(t.starttime+level-1) as "Date", 
       case 
       when trunc(t.starttime+level-1) = trunc(t.starttime) then
            extract(hour from trunc(t.starttime+level)
                  -greatest(t.starttime,trunc(t.starttime+level-1)))     
       when trunc(t.starttime+level-1) = trunc(t.endtime) then     
            extract(hour from t.endtime-trunc(t.endtime))
       else
            24     
       end as "Spend hour"   
  from t
 connect by level <= extract(day from t.endtime - t.starttime)+1;

Essayez ce frère, vous pouvez changer la sélection finale en "SELECT * FROM tbl" pour voir comment elle s'exécute.

;WITH tbl AS 
(
    SELECT "starttime" AS Date0,
        DATEADD(HH,24-DATEPART(HH,"starttime"),"starttime") AS "endtime0",
        DATEDIFF(HH,"starttime",DATEADD(HH,24-DATEPART(HH,"starttime"),"starttime")) AS SPENTTIME,
        "endtime"
    FROM T
    UNION ALL
    SELECT Date0+1,"endtime0"+1,
    CASE WHEN "endtime0"+1>"endtime" THEN DATEDIFF(HH,Date0+1,DATEADD(HH,24-DATEPART(HH,"endtime"),Date0+1))
        ELSE 
        DATEDIFF(HH,Date0+1,DATEADD(HH,24,Date0+1)) END AS SPENTTIME ,
        "endtime"
    FROM tbl
    WHERE Date0+1<"endtime"
) 
SELECT CONVERT(NVARCHAR(20),Date0,111) AS Date,SPENTTIME
FROM tbl