Intervals between timestamps in hours with fractions of a day for interval ends

[alex_bernea]

Hello,

I am trying to break the interval between 2 timestamps in order to find out the difference in hours per day with fractions for intervals

I have the following input data:

StartTime	EndTime
02/08/17 20:44	02/10/17 04:52

I would like to show them as:

StartTime	EndTime	Time elapsed
02/08/17 20:44	02/09/17 00:00	03:16
02/09/17 00:00	02/10/17 00:00	24:00
02/10/17 00:00	02/10/17 04:52	04:52

I tried while loops and intervals, but I can't find a way to break correctly the edges of the intervals (those that are not full day)

Thank you,

Alex

[Anonymous]

Try sing interval function explained here.

something like interval(Endtime-StartTime,'HH:MM')

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

  Interval(EndTime - StartTime, 'hh:mm') as [Time Elapsed];

LOAD TimeStamp(RangeMax(DayStart(StartTime + IterNo()-1), StartTime)) as StartTime,

  TimeStamp(RangeMin(DayStart(StartTime + IterNo()-1, 1), EndTime)) as EndTime

While Floor(StartTime) + IterNo() <= Floor(EndTime + 1);

LOAD * INLINE [

    StartTime, EndTime

    02/08/17 20:44, 02/10/17 04:52

    03/08/17 10:44, 03/10/17 17:52

];

[alex_bernea]

It works. Thannk you