Summary of duration field.

[Amit_B]

Hi,

There is a field for 'Waiting Time' in the format DDD:hh:mm:ss.

I need to create (in script) 3 new columns that summaries this time - summary in days, summary in hours, and summary in minutes.
The seconds are always rounds up. Even if only one second has passed, it should be considered as a full minute.

For example:
For the value 001:12:30:01 - the summary of minutes is 2,191 (36h*60 + 30 + 1s=1m), the summary of hours is 36.52 (36h & 31/60=0.516), and the summary of days is 1.52 (1D & 12.52/24=0.521).

I hope this is clear.
I would appreciate any help.

[HeshamKhja1 · Accepted solution]

Hi @Amit_B ,

Try the code below:

LOAD *,
	Num(SubField(times, ':', 1) + (SubField(times, ':', 2)/24) + ((SubField(times, ':', 3) + (1+(SubField(times, ':', 4) = 0)))/1440), '##.##') AS days,
    Num((SubField(times, ':', 1)*24) + SubField(times, ':', 2) + ((SubField(times, ':', 3) + (1+(SubField(times, ':', 4) = 0)))/60), '##.##') AS hours,
    Num((SubField(times, ':', 1)*1440) + (SubField(times, ':', 2)*60) + SubField(times, ':', 3) + (1+(SubField(times, ':', 4) = 0)), '##.') AS minutes,
    1+(SubField(times, ':', 4) = 0) AS seconds
	;
LOAD
	Interval(Interval#('001:12:30:01', 'DDD:hh:mm:ss'), 'DDD:hh:mm:ss') AS times
AutoGenerate(1);

I am basically dividing each part of the time stamp and converting it by multiplication/division. The seconds field is just a way to turn any values to 1 unless it is zero.

Let me know if it works.