Compare three date/time ranges

[abbam]

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

[TISKAR]

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

[p_gurav]

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")

[koshyk]

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")

[abbam]

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?

[koshyk]

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

[deepashri_123]

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!!

[abbam]

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?

[somesoni2]

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")

[abbam]

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?