Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
Try sing interval function explained here.
something like interval(Endtime-StartTime,'HH:MM')
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
];
It works. Thannk you