Hi All,
I need to find the difference between these two dates with the removal of the weekends
I have 2 date value fields as
ASSIGNED_DT = 2018-08-30 15:33:51
ANSWER_DT= 2018-09-03 16:59:48
| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S") | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S") | eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = tostring(( Answer_Time - Assigned_Time), "duration") | table ASSIGNED_DT, ANSWER_DT, diff, WeekendDays
Everything is working fine and the results are:-
ASSIGNED_DT ANSWER_DT diff WeekendDays
2018-08-22 15:33:51 2018-09-03 16:59:48 12+01:25:57.000000 4
Now I just need help with:
1. remove the WeekendDays from the diff
2. Convert diff-WeekendDays as the only number of days in decimal: for example here : it should be 8.01 days or 8 days 1 hour 25 mins only.
Thanks for your help.
hi @Chandras11
can u try like this
| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S") | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S") | eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = ( Answer_Time - Assigned_Time) |eval diff=(diff-(WeekendDays*86400)) | eval diff = tostring(diff, "duration") | table ASSIGNED_DT, ANSWER_DT, diff, WeekendDays
Hi @harishalipaka ,
Could you also please provide the query to find the difference in hours instead of day's by excluding weekend days.
because in my similar scenario there would be a chance where assigned and answer date is same day.
Thanks in Advance..!!!
hi @Chandras11
can u try like this
| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S") | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S") | eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = ( Answer_Time - Assigned_Time) |eval diff=(diff-(WeekendDays*86400)) | eval diff = tostring(diff, "duration") | table ASSIGNED_DT, ANSWER_DT, diff, WeekendDays
Hi,
Thanks for the response. Yes, it's working perfectly.
Can we convert the diff 8+01:25:57.000000 into days with decimal values: for example 8.01 days here
BR,
Chandra
hi @Chandras11
try like this
| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S") | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S") | eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = ( Answer_Time - Assigned_Time) |eval diff=(diff-(WeekendDays*86400)) | eval diff = tostring(diff, "duration") | table ASSIGNED_DT, ANSWER_DT, diff, WeekendDays |eval temp=split(diff,"+") |eval temp2=split(mvindex(temp,1),":") |eval diff=mvindex(temp,0)+"."+mvindex(temp2,0)+" "+"days" | table ASSIGNED_DT, ANSWER_DT, diff, WeekendDays
perfect.. thanks a lot for it 🙂
its working fine...