topic Re: Difference between two string date fields in Splunk Search

Difference between two string date fields

perryd — Wed, 17 Apr 2019 09:20:45 GMT

Hi, i searched but i don't found any solution. I wont the difference between two fields that are date in string format. My field are:

StartDate: String->dd/mm/yyyy;
EndDate: String-> dd/mm/yyyy; Its possible obtain the difference in days? For example: in first row, i've 01/01/2017 and 01/07/2017. My result must be 183.

I don't understand how to convert my string date in values day.

Re: Difference between two string date fields

adonio — Wed, 17 Apr 2019 09:33:01 GMT

hello there,

run this search anywhere and apply the logic to address your challenge.
here i used eval strptime and leverage the epoch numeric value to calculate gap

| makeresults count=1
| eval start_date = "01/01/2017"
| eval end_date="01/07/2017"
| rename COMMENT as "above creates fake data, below is your solution" 
| eval start_epoch = strptime(start_date, "%d/%m/%Y")
| eval end_epoch = strptime(end_date, "%d/%m/%Y")
| eval gap_in_seconds = end_epoch - start_epoch
| eval gap_in_days = round(gap_in_seconds / 86400)

further reading:
https://docs.splunk.com/Documentation/Splunk/7.2.5/SearchReference/Commontimeformatvariables

hope it helps

Re: Difference between two string date fields

kamlesh_vaghela — Wed, 17 Apr 2019 09:43:44 GMT

@perryd

The difference between these two dates is 181. And if you want to add last day also in your count then add 1 in your search. Is that any specific calculation for getting 183?

| makeresults 
| eval start_date="01/01/2017",end_date="01/07/2017" 
| eval dates=mvcount(mvrange(strptime(start_date,"%d/%m/%Y"),strptime(end_date,"%d/%m/%Y"),86400))

| eval dates=dates+1

Re: Difference between two string date fields

perryd — Wed, 17 Apr 2019 10:06:12 GMT

Hi, don't work. Specifically, when i use the function strptime(StardDate) or strptime(EndDate) i lost all data in these fields. Splunk don't convert my string in strptime, so, When i try to do difference between startdate and enddate i don't have any output.

Re: Difference between two string date fields

kamlesh_vaghela — Wed, 17 Apr 2019 10:08:37 GMT

Can you please share some more information about your events and fields (StardDate, EndDate & others)?

Re: Difference between two string date fields

perryd — Wed, 30 Sep 2020 00:11:08 GMT

I've one file CSV. In this file i have some fields, two of this are date. Splunk read this date like a strings. Now, i have need to calcolate the difference between this two dates, row-by-row. My final output must be a new column with all difference of this dates in days. i wrote 183 days, but was an example. I want all difference, for any row and any dates, in day, only this.

I try to write this:
...
| eval start_epoch = strptime(StardDate, "%d/%m/%Y")
| eval end_epoch = strptime(EndDate, "%d/%m/%Y")
| eval gap_in_seconds = end_epoch - start_epoch
| eval gap_in_days = round(gap_in_seconds / 86400)
and my output is null. Splunk don't convert my string date in strptime, if i try to write only " eval start_epoch = strptime(StardDate, "%d/%m/%Y")" i don't see anythings, i don't have output.

Re: Difference between two string date fields

kamlesh_vaghela — Wed, 17 Apr 2019 11:45:14 GMT

@perryd

I have tried with below CSV content. It's working. Is that any space OR double quotes in your CSV content?

StardDate,EndDate
01/01/2017,01/07/2017
01/01/2017,01/08/2017
01/01/2017,01/09/2017
01/01/2017,01/10/2017

Re: Difference between two string date fields

perryd — Wed, 17 Apr 2019 11:55:00 GMT

Can i see how work on your splunk? It make the difference between these dates? (in day). Because my CVS is not controllable from me, i can't modify it. I can try to upload a my file with some dates and do test for it.

Re: Difference between two string date fields

kamlesh_vaghela — Wed, 17 Apr 2019 12:04:02 GMT

You can share your CSV data here OR data from search, using | inputlookup. So we can look at that.

Re: Difference between two string date fields

perryd — Wed, 17 Apr 2019 12:09:23 GMT

I've data protect from non disclosure agreement. Now i try with a my CSV test and i see if run. If i continue to have problem i ask here, thank you.

Re: Difference between two string date fields

kamlesh_vaghela — Wed, 17 Apr 2019 12:11:53 GMT

Glad to help you.

Happy Splunking

Re: Difference between two string date fields

beingkaran — Wed, 30 Sep 2020 03:53:20 GMT

HI Perry are your dates in 01/01/2017 format or 01-01-2017 format coz that will change the time format we are giving in order to convert the epoch strings.

SO the strptime
eval start_epoch = strptime(StardDate, "%d/%m/%Y")
will become
eval start_epoch = strptime(StardDate, "%d-%m-%Y")

I was making the same mistake