I have an index that has the fields start date and end date. I need to find the difference between the two timestamps, convert it into days, and put in different duration buckets.
Following is an example of the data:
ID START_DATE END_DATE
1 1970-03-12 00:00:00.0 2020-06-17 00:00:00.0
2 2015-02-01 00:00:00.0 2020-01-02 00:00:00.0
and so on.
My query looks like:
index={something}
| where START_DATE!="" AND END_DATE!=""
| eval difftime=strptime(END_DATE,"%Y-%m-%d %H:%M:%S.%3N")-strptime(START_DATE,"%Y-%m-%d %H:%M:%S.%3N")
| eval daydiff = round(difftime/86400)
| eval Label=case(
daydiff <= 30, "<=30 Days",
daydiff > 30 AND daydiff <= 90, ">30 Days AND <= 90 Days",
daydiff > 90 AND daydiff <= 365, ">90 Days AND <= 12 Months",
daydiff > 365 AND daydiff <= 730, ">12 Months AND <= 24 Months",
daydiff > 730 AND daydiff <= 1095, ">24 Months AND <= 36 Months",
daydiff > 1095, ">36 Months")
| stats count(ID) as Counts by Label
| eval SortLabel = case(Label="<=30 Days",1,Label=">30 Days AND <= 90 Days",2,Label=">90 Days AND <= 12 Months",3,Label=">12 Months AND <= 24 Months",4,Label=">12 Months AND <= 24 Months",5,Label=">24 Months AND <= 36 Months",6)
| sort SortLabel
| table Label Counts
Problem: When the start date is in 1970, strptime isn't returning anything at all (which I think is a known issue), which is giving me wrong counts.
A workaround which I thought was to add an if statement wherever I'm doing the conversion, and hardcode it to 0. But that won't work if, let's say, the start date and end date are both in 1970. In that case, both would be 0, and the count for the first label would increase whereas the count for the appropriate duration bucket should increase.
Is there a way to do this? Is there any other function to get the UNIX time Or, is there a better way to do this?
Alternatively, can I find the difference between the two times directly somehow?
