Calculate number of days (exclude weekends) between 2 dates?

[dominhthe110]

Hi everyone, 

I want to calculate the number of days (exclude weekends) between 2 days with the same format of datetime (Ex: 2018-07-21 09:55:51). I followed the solution in the post right here: How do I calculate the date difference for two timestamps other than _time and exclude weekends?  and adjusted the answer to fit my data but there was no results returned.

So I removed a few lines of code in the solution above to understand the problem: 

index="orders_csv_local"
|eval start=strptime(start_date,"%Y-%m-%d")
|eval end= strptime(end_date,"%Y-%m-%d")
|eval dates= mvrange(end,start,86400)
|table start end dates

At this stage, there was no value returned for the third column "dates"? I'm wondering why the 4th line didn't return anything.  

Please advice me on how to resolve this problem in order to get the final result. Thank you so much in advance.

[rnowitzki]

Hi @dominhthe110 ,

If your start- and enddate includes hours, minutes and seconds as given in your example (2018-07-21 09:55:51), then you have to include it in the strptime:

|eval start=strptime(start_date,"%Y-%m-%d %H:%M:%S")

 
Hope it helps.
BR
Ralph

[isoutamo]

Hi

This is working. Be sure that you have in the right order start and enddate on mvrange!

| makeresults 
| eval start_date="2020-07-01 09:55:51", end_date="2020-07-23 01:01:01" 
| eval start=strptime(start_date,"%F %T") 
| eval end= strptime(end_date,"%F %T") 
| eval dates= mvrange(start,end,86400)
| convert ctime(dates) timeformat="%A" 
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) 
| eval noOfDays=mvcount(dates) 
| table start end dates noOfDays

[dominhthe110]

Thank @isoutamo ,

Your answer helped me. It worked.
But in a case that I want the result is a negative number between the start and the end day. For example, the duration as days between the "estimated delivered date" and the "actual delivered date" of a shipping package: If the actual date is "2018-04-13 00:00:00" and the estimated one is "2018-04-15 00:00:00", the result will be -2 days. What is the SPL to calculate the outcome for this case?

[isoutamo · Accepted solution]

This is one way to do it.

| makeresults 
| eval start_date="2020-08-01 09:55:51", end_date="2020-07-23 01:01:01" 
| eval start=strptime(start_date,"%F %T") 
| eval end= strptime(end_date,"%F %T") 
| eval sign=if(start > end, -1, 1), startD=if(start>end, end, start), endD=if(start>end, start, end)
| eval dates= mvrange(startD,endD,86400)
| convert ctime(dates) timeformat="%A" 
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) 
| eval noOfDays=mvcount(dates) * sign
| table start end dates noOfDays

r. Ismo