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:

| 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


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


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

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
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...