Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date format like
[Create Time Release] [Release Time])
2015-12-17 09:52:24 AM 2015-12-17 09:58:22 AM
I used following script to find the diffrence in HH:MM:SS, But its not working for me.
Interval(
rangesum(
NetWorkDays([Create Time Release] +1,[Release Time]-1) * MakeTime(9) // 8 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays([Release Time],[Release Time]),Rangemin(rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30))-Rangemax(rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)),0) // working hours last day
,if(NetWorkDays([Create Time Release] ,[Create Time Release] ),Rangemin(rangemax(frac([Create Time Release] ),maketime(16,30)),maketime(16,30))-Rangemax(rangemin(frac([Create Time Release] ),maketime(16,30)),maketime(7,30)),0) // working first day
,if(NetWorkDays([Create Time Release] ,[Create Time Release] ) and floor([Create Time Release] )=floor([Release Time]),-MakeTime(7,30)) // correct for first equals last day
)
)
as DurationRelease,
working hours 7:30 to 4:30 and used TimestampFormat= 'YYYY-MM-DD hh:mm:ss[.fff] TT';
Thanks,
As follow Sasidhar
Might be like this
interval(timestamp(timestamp#('2015-12-17 09:58:22 AM','YYYY-MM-DD hh:mm:ss TT'))-timestamp(timestamp#('2015-12-17 09:52:24 AM','YYYY-MM-DD hh:mm:ss TT')),'YYYY-MM-DD hh:mm:ss TT')
In the simplest form:
Interval([Release Time] - [Create Time Release], 'hh:mm:ss') as DurationRelease
if you have loaded the two fields as proper QV numeric date/time fields. If thay are still string values, then you will need
Interval(Timestamp#([Release Time], 'yyyy-MM-dd hh:mm:ss TT') - Timestamp#([Create Time Release], 'yyyy-MM-dd hh:mm:ss TT'), 'hh:mm:ss') as DurationRelease
Although I recommend that the Timestamp#()' s are in your load script so that you are storing date/time values and you can use the simple expression above.
Your expression could be rendered:
Interval(
Rangesum(
NetWorkDays([Create Time Release] + 1, [Release Time] - 1) * 9/24,
If(NetWorkDays([Release Time], [Release Time]),
Rangemin(Rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30)) -
Rangemax(Rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)), 0),
If(NetWorkDays([Create Time Release] ,[Create Time Release]),
Rangemin(Rangemax(frac([Create Time Release]),maketime(16,30)),maketime(16,30)) -
Rangemax(rangemin(frac([Create Time Release]),maketime(16,30)),maketime(7,30)), 0),
If(NetWorkDays([Create Time Release],[Create Time Release])
And floor([Create Time Release]) = floor([Release Time]),
-MakeTime(7,30))
)
'hh:mm:ss') as DurationRelease,
This is syntactically correct, but I am not sure if is logically correct as well.
If one of the answers is correct, please mark it as so.
Do you still have issues with that expression (which I remember quite well, even if it's some years old)?
If so, it would be really helpful if you actually would describe your issues in details, giving some sample input records, returned results from the expression and the results you expect to get instead.
From what I can see for now, you have a 9 hour working day, so the correction for same start and end date should be
Interval(
Rangesum(
NetWorkDays([Create Time Release] + 1, [Release Time] - 1) * 9/24,
If(NetWorkDays([Release Time], [Release Time]),
Rangemin(Rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30)) -
Rangemax(Rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)), 0),
If(NetWorkDays([Create Time Release] ,[Create Time Release]),
Rangemin(Rangemax(frac([Create Time Release]),maketime(16,30)),maketime(16,30)) -
Rangemax(rangemin(frac([Create Time Release]),maketime(16,30)),maketime(7,30)), 0),
If(NetWorkDays([Create Time Release],[Create Time Release])
And floor([Create Time Release]) = floor([Release Time]),
-MakeTime(9))
)
'hh:mm:ss') as DurationRelease,
If you issue is something else, please clarify (and also double check that your timestamps are actually read in correctly from your source: