topic Re: How do I force a timestamp to be recognized as UTC in a query for strptime? in Splunk Search

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

briancronrath — Tue, 07 Aug 2018 18:02:21 GMT

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?

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

kmaron — Tue, 07 Aug 2018 20:23:25 GMT

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)

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

pgoldweic — Thu, 05 Dec 2019 18:02:03 GMT

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.

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

pgoldweic — Thu, 05 Dec 2019 18:05:48 GMT

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?

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

pgoldweic — Fri, 06 Dec 2019 20:19:47 GMT

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.

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

pgoldweic — Fri, 06 Dec 2019 20:21:36 GMT

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.

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

to4kawa — Fri, 06 Dec 2019 22:06:02 GMT

| 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?

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

pgoldweic — Fri, 06 Dec 2019 23:03:20 GMT

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!

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

to4kawa — Fri, 06 Dec 2019 23:19:22 GMT

I see, Thank you for the detailed explanation.

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

bowesmana — Tue, 31 Jan 2023 22:24:33 GMT

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.