topic Re: Compare three date/time ranges in Splunk Search

Compare three date/time ranges

abbam — Thu, 29 Mar 2018 09:17:51 GMT

Hi All,

I have three dates which I need to compare, the dates that I have is:

date1=03/29/2018 04:59:26 #this can be any date/time and changes
date2=03/28/2018 12:00:00.000000
date3=03/29/2018 12:00:00.000000

I want to check whether the date1 falls between date2 and date3 - can this be done?

If date1 falls between date2 AND date3, assign value X, if NOT, assign value Y

Thanks

Re: Compare three date/time ranges

somesoni2 — Thu, 29 Mar 2018 09:41:28 GMT

Assuming your date fields are in string format (as oppose to epoch format), you can try something like this:

your current search which includes field date1 date2 and date3
| eval YourField=if(strptime(date1,"%m/%d/%Y %H:%M:%S")>=strptime(date2,"%m/%d/%Y %H:%M:%S.%6N") AND strptime(date1,"%m/%d/%Y %H:%M:%S")<strptime(date3,"%m/%d/%Y %H:%M:%S.%6N"),"X", "Y")

Re: Compare three date/time ranges

deepashri_123 — Tue, 29 Sep 2020 18:49:18 GMT

Hey@abbam,

Convert your date in epoch time.
eval date1_epoch = strptime('date1', "%d/%m/%Y %H:%M:%S")
and similarly for other dates and then you can compare the epoch time .

You can try something like this:
eval status=if(date1_epoch>date2_epoch AND date3_epoch>date1_epoch,"X","Y")

Let me know if this helps!!

Re: Compare three date/time ranges

koshyk — Thu, 29 Mar 2018 09:45:43 GMT

Please find an example

| makeresults 
|  eval date1="03/29/2018 04:59:26" 
|  eval date2="03/28/2018 12:00:00.000000" 
|  eval date3="03/29/2018 12:00:00.000000" 
|  eval date1epoch=strptime(date1,"%m/%d/%Y %H:%M:%S") 
|  eval date2epoch=strptime(date2,"%m/%d/%Y %H:%M:%S.%6N") 
|  eval date3epoch=strptime(date3,"%m/%d/%Y %H:%M:%S.%6N") 
|  eval status=if(date1epoch>date2epoch AND date1epoch<date3epoch,"BETWEEN","OUTSIDE")

Re: Compare three date/time ranges

abbam — Thu, 29 Mar 2018 11:10:58 GMT

Thanks,
The way that i am getting the date1 is by doing earliest(_time) in a stats count.

From my understanding, this is EPOCH.

I have tried your query and it doesn't seem to work.

Any ideaS?

Re: Compare three date/time ranges

abbam — Thu, 29 Mar 2018 11:24:59 GMT

Thanks,

The date1 field is from stats earliest(_time).

The date2 and date3 are from:

| eval today=relative_time(now(),"@d") 
| eval date2=relative_time(today,"-12h@h")
| eval date3=relative_time(today,"+12h@h")

Any ideas how this can work?

Re: Compare three date/time ranges

abbam — Thu, 29 Mar 2018 11:25:18 GMT

thanks @deepashri_123

Thanks,

The date1 field is from stats earliest(_time).

The date2 and date3 are from:

| eval today=relative_time(now(),"@d") 
| eval date2=relative_time(today,"-12h@h")
| eval date3=relative_time(today,"+12h@h")

Any ideas how this can work?

Re: Compare three date/time ranges

p_gurav — Thu, 29 Mar 2018 11:28:34 GMT

can you try this:

       | eval today=relative_time(now(),"@d") 
       | eval date2=relative_time(today,"-12h@h")
       | eval date3=relative_time(today,"+12h@h")  
       |  eval status=if(today>date2 AND today<date3,"BETWEEN","OUTSIDE")

Re: Compare three date/time ranges

koshyk — Thu, 29 Mar 2018 14:00:11 GMT

eh.. which Splunk version you running? working for me on 6.4+ versions and works.

Only reason I can think of is:
I've just used your date example format. You might need to change the format in "strptime" accordingly to precise output you are getting in earliest(_time) as it is "locale" driven

Re: Compare three date/time ranges

TISKAR — Thu, 29 Mar 2018 14:42:51 GMT

Try this,

| makeresults | eval today=relative_time(now(),"@d") 
| eval date2=relative_time(today,"-12h@h") 
| eval date3=relative_time(today,"+12h@h") 
| eval status=case((today>date2 AND today<date3) OR (today<date2 AND today>date3),"BETWEEN",true(),"OUTSIDE")

Regards