Splunk Search

How do I force a timestamp to be recognized as UTC in a query for strptime?

briancronrath
Contributor

I have a datasource that passes the time as a string like the following: "2018-08-07T17:38:16.352"

This string is in UTC time.

How am I able to get this to just recognize properly as being in UTC using strptime? No matter what I do it either converts to my local timezone or just doesn't convert it at all and throws it out. I've tried:

| eval ts=strptime(ts,"%Y-%m-%dT%H:%M:%S")

|eval ts=strptime(ts,"%Y-%m-%dT%H:%M:%S.%3N")

|eval ts=strptime(ts." UTC","%Y-%m-%dT%H:%M:%S.%3N %Z")

|eval ts=strptime(ts." GMT","%Y-%m-%dT%H:%M:%S.%3N %Z")

|eval ts=strptime(ts." +0000","%Y-%m-%dT%H:%M:%S.%3N %z")

|eval ts=strptime(ts." 0000","%Y-%m-%dT%H:%M:%S.%3N %z")

|eval ts=strptime(ts." 00","%Y-%m-%dT%H:%M:%S.%3N %z")

Absolutely none of these work. How can I just get this to simply convert properly to the epoch value as if this time string were in UTC?

Tags (2)
0 Karma
1 Solution

kmaron
Motivator

try this:

| eval epoch_time = strptime(st, "%FT%T.%3N%z")

I used this to verify:

| makeresults 1
| fields - _time
| eval st = "2018-08-07T17:38:16.352"
| eval epoch_time = strptime(st, "%FT%T.%3N%z")
| eval utc_time = relative_time(epoch_time,strftime(epoch_time,"%z")."h")
| convert ctime(utc_time)

View solution in original post

to4kawa
Ultra Champion
| makeresults 
| eval localtime = strftime(_time, "%F %T.%3N")
| eval epoch_time = _time
| eval time_suffix=strftime(epoch_time,"%:::z")
| eval time_suffix_mod=if(substr(time_suffix,1,1)=="+","-".substr(time_suffix,2),"+".substr(time_suffix,2))
| eval unix_time=relative_time(epoch_time,(time_suffix_mod."h"))
| eval unix_time=strftime(unix_time, "%F %T.%3N")

Hi, @pgoldweic
There were certainly various problems.
How about this?

0 Karma

pgoldweic
Communicator

Thanks for replying, although I had already figured out the solution, as I pointed out in my comments. I'm not quite sure I've followed fully your suggested solution (and it does not seem to apply as well as the one given by kmaron actually, or perhaps because I'm misunderstanding it). In any case, here's what worked for me:
| makeresults
| eval st = "2018-08-07 17:38:16.352"."-0000"
| eval epochTime = strptime(st, "%F %T.%3N%z")

This ensures that then I can format the epochTime to display in local time as follows:
| eval newlyformattedString = strftime(epochTime, ""%F %T.%3N%z")

and this displays as "2018-08-07 12:38:16.352-0500" , which is exactly what I needed. Hope this makes sense. Thanks again for your reply!

to4kawa
Ultra Champion

I see, Thank you for the detailed explanation.

0 Karma

pgoldweic
Communicator

Did the solution proposed work for you? I've also been having a similar issue but with a string that does not have the 'T' itself, although it is encoded as UTC nonetheless. I cannot get the proposed solution to work for me.

0 Karma

pgoldweic
Communicator

Just repeating what I explained as a comment to the answer below: I ended up appending to my strings the "-0000" suffix, and then using your suggestion to obtain the epoch_time. My format string simply omits the "T" in between the %F and %T formatting to apply to my case.

0 Karma

kmaron
Motivator

try this:

| eval epoch_time = strptime(st, "%FT%T.%3N%z")

I used this to verify:

| makeresults 1
| fields - _time
| eval st = "2018-08-07T17:38:16.352"
| eval epoch_time = strptime(st, "%FT%T.%3N%z")
| eval utc_time = relative_time(epoch_time,strftime(epoch_time,"%z")."h")
| convert ctime(utc_time)

bowesmana
SplunkTrust
SplunkTrust

Note that this statement in this solution is wrong

| eval utc_time = relative_time(epoch_time,strftime(epoch_time,"%z")."h")

as it will convert offset to a 4 digit TZ offset (in my case +1100) and append h, so will do a relative_time addition of 1100 hours to my time, whereas it should be +11h.

0 Karma

pgoldweic
Communicator

I have an analogous problem although my strings do not include the "T" in between the date and time, although they are UTC though. When I try your solution, I get no value for the epoch_time field. How would you modify the format string to work in my case?

0 Karma

pgoldweic
Communicator

I think I've figured this out. I ended up appending to my strings the "-0000" suffix, and then using your suggestion to obtain the epoch_time.
My format string simply omits the "T" in between the %F and %T formatting to apply to my case.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...