Why is the strptime() eval not returning anything?

[loganjwb]

I am using imported CSV data to search throughout Splunk and the CSV file defines the column TIME and only includes the year and month in the format YYYY-MM. I am attempting to convert that field into a UTC UNIX timestamp using the strptime() function but have not had any success. 

This is an image of the extracted fields with a basic search:

These were the searches I used when attempting to use the strptime() function. All of the examples did not work.

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=strptime(TIME, "%Y-%m")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=TIME."-00:00:00:00", TIME=strptime(TIME, "%Y-%m-%d:%H:%M:%S")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval my_time=strptime('TIME', "%Y-%m")

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval my_time=strptime(YEAR.MONTH, "%Y-%m")

Additionally, I also tried using the convert command and that didn't work either. Both of the examples below did not work.

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | convert timeformat="%Y-%m" mktime(TIME) AS NEW_TIME

index="financial_data" source="consumer_confidence_index.csv" LOCATION=USA | eval TIME=TIME."-00:00:00:00" | convert timeformat="%Y-%m-%d:%H:%M:%S" mktime(TIME) AS NEW_TIME

Any advice is appreciated, thank you.

[shivanshu1593 · Accepted solution]

Hello,

It won't return any results because Splunk won't be able to calculate the epoch time due to the format of the TIME field that you have. Epoch time is the number of seconds elapsed since January 1st, 1970. Since your field TIME does not contain the day of the month, no results would be returned.

The minimum requirement of epoch time conversion is YYYY-MM-DD (which Splunk should handle by adding the first day of the month automatically). If you can add the day in the TIME variable (Ex: 2022=05-12), then you'll be able to perform the operation. Following is a run anywhere example for the same.

| makeresults
| eval TIME = now()
| eval TIME = strftime(TIME, "%Y-%m-%d")
| fields - _time
| eval TIME = strptime(TIME, "%Y-%m-%d")

Hope this helps. 
###If this helps, kindly consider accepting as an answer/upvote###

[PickleRick]

Sorry, but I beg to differ here as to the cause. Any timestamp format that doesn't include precise information up to the second resolution "doesn't contain absolute time". Yet, for most cases strptime simply assumes the unfilled fields are at their minimum values and gets on with it. So if you don't give hour or minute information, it will happily accept that it's midnight or a full hour. Logically, it should do the same here - assume that as we're not providing a day number, it's the first of the month, the midnight starting the month. But for some reason sometimes splunk doesn't parse such timestamps properly. Maybe it assumes that any non-filled value defaults to 0 and with day number it's kinda ridiculous assumption. But that's just my wild guess.

[shivanshu1593]

Agreed. The term absolute time shouldn't have been used in the answer. I have edited it. Was thinking in a different direction, typed half of the answer and then the chain of thoughts changed. Since Splunk handles the missing %H:%M:%S gracefully, it should with the date too. 

[PickleRick]

Well... strange. Apparently, strptime() doesn't parse the date in some cases. The workaround here would be to "glue" a constant "-01" to the date and strptime with "%Y-%m-%d" but in general, it looks like a bug.

[shivanshu1593]

Agreed. Since Date is a mandatory requirement for calculating Epoch time, Splunk might consider adding the first day of the month by itself., instead of throwing blank results.