topic Re: Date and time conversion in Splunk Search

Date and time conversion

nate_washburn — Fri, 21 Jan 2022 16:51:50 GMT

Hi, in my index I have a couple time fields that are returned via a simple search

_time = 1/20/2022 1:38:55.000 PM (the Splunk-generated time)
body.timestamp = 2022-01-20T21:38:45.7774493Z (the transaction time from our log)

I am trying to format the time output with the convert function but can only get the first result to return.

| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS timestamp = 2022-01-20 21:38:55
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(body.timestamp) AS timestamp2 = none

Am I missing something for the second timestamp to be returned? Thanks!

Re: Date and time conversion

diogofgm — Fri, 21 Jan 2022 17:18:31 GMT

Yes, you are. 🙂 ctime expects an epoch which _time is and your body.timestamp is not.

Fear not as you can change that with strptime. Also you need to rename the field to something without "." as some things do not with with . on field names.

| makeresults | eval body.timestamp="2022-01-20T21:38:45.7774493Z" | rename "body.timestamp" AS timestamp2 | eval timestamp2 = strptime(timestamp2,"%Y-%m-%dT%H:%M:%S.%7NZ")

After this you can use ctime or strftime to format the timestamp to what you want.

Re: Date and time conversion

nate_washburn — Fri, 21 Jan 2022 18:19:45 GMT

Thanks @diogofgm you got me started down the correct path. With a little tweeking, here is what worked for me.

| eval body.timestamp=strptime('body.timestamp',"%Y-%m-%dT%H:%M:%S.%7NZ") | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(body.timestamp) as timestamp2