Date and time conversion

[nate_washburn]

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

1. _time = 1/20/2022 1:38:55.000 PM (the Splunk-generated time)
2. 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!

[nate_washburn]

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

[diogofgm]

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.