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

[briancronrath]

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?

[to4kawa]

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

[pgoldweic]

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]

I see, Thank you for the detailed explanation.

[pgoldweic]

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.

[pgoldweic]

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.

[kmaron · Accepted solution]

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]

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.

[pgoldweic]

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?

[pgoldweic]

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.