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!

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...