Splunk Search

Problem with Date Time Manipulation for date in the year 1970

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? 

Labels (3)
0 Karma
1 Solution

Communicator

If the date starts with 1970, make it 1971, then subtract 365*24*60*60 from the parsed date

View solution in original post

0 Karma

Communicator

If the date starts with 1970, make it 1971, then subtract 365*24*60*60 from the parsed date

View solution in original post

0 Karma

Thanks. I used a similar logic to replace the years if they are before 1971 with 1971 and subtract the seconds in years multiplied by the difference between 1971 and the given year

0 Karma