How do I convert a timestamp from one timezone to another (inline)?

[weidertc]

I have an issue where logs contain timestamps in zulu and the server uses local time for its index.  I need to calculate delays now since the logs are initially written on vendor servers and there can be between 0 delay or over an hour behind.

I can't find any functions that do this and am not having luck either converting a timestamp from one timezone to another or converting timezones to seconds to do any math on.

timestamp log was written

2020-07-22T12:59:12.301063Z

timezone log was indexed from _time

-0400

I am not an admin and have no control or influence over the servers or its configuration.

Is there an easy way to do this inline in the query?

[to4kawa]

| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| eval UTC=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval EST=ceil(relative_time(UTC,"-4h")).".".mvindex(split(UTC,"."),1)
| convert ctime(UTC) ctime(EST)

how about this? It isn't very easy, I guess.

[weidertc]

The problem with this is that the offset is hardcoded.  I cannot determine the time zone of the server as it changes and I need some SPL that is aware of the server's timezone.

This may be enough though.  It looks like I need a table of timezone offsets and its relative time equivalent (e.g. -4h) then i can extract the timezone from _time and look it up in the table to get the relative time equivalent.

It's unfortunate that it's this hard.

Thanks

[to4kawa]

| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| eval UTC=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval my_local_TZ=mvindex(split(strftime(UTC,"%FT%T %:::z")," "),1)
| eval my_local_time=ceil(relative_time(UTC,my_local_TZ."h")).".".mvindex(split(UTC,"."),1)
| convert ctime(UTC) ctime(my_local_time)

you don't need hard coded.

[senior_splunk]

@to4kawa  thank you for this but I do have a question as I am still fairly new to Splunk.  How can I have the corrected time not show the microseconds?

I have this:   08/17/2020 10:18:43.071000

and I need this:  08/17/2020 10:18:43

 Thanks in advance!

[to4kawa]

@senior_splunk 

I can't tell you anything about the time without knowing the details.

First, please clarify whether you are talking about logs, display or field processing.

[senior_splunk]

@to4kawa  apologies, I should have been more clear.  The output of your code produces time that is adjusted correctly but includes microseconds in the output.  I was simply looking for a way to produce the output without them.

A co-worker who is much better with splunk than I am gave me the following which does the trick

rex field=my_local_time mode=sed "s/\..*$//"

[to4kawa]

| eval my_local_time=ceil(epoch).".".mvindex(split(epoch,"."),1)

My query is deliberately adding microseconds here.

just simply:

| eval my_local_time=ceil(epoch)

@senior_splunk 

[weidertc]

This is an improvement.  For some reason though it doubles the offset.  i am at -4h, but 2020-07-22T12:59:12.301063Z is reduced by 8 to become 07/22/2020 04:59:12.301063.

I think Splunk strptime() is converting the timezone.  It uses the timezone of the logged in user instead of the server local time.  It'll only work if i am in the same timezone as the server, which is fine for me but not usually the case with others, and then the rest of the lines re-apply the timezone to double it.

[to4kawa]

_time is epoch time. default  is UTC. when it displays, it changes user preference time zone.

https://en.wikipedia.org/wiki/Unix_time

if index setting is good, time displays appropriate time zone. it is no problem. 

[weidertc]

What I'm saying is the offset is doubled with your SPL.  I don't think Splunk will let you use strptime or strftime in a solution because it uses your local time which can't be a factor.  I am only trying to work with vendor time and splunk server local time.

[to4kawa · Accepted solution]

| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| fields - _time
| eval epoch=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval my_local_TZ=mvindex(split(strftime(epoch,"%FT%T %:::z")," "),1)
| eval my_local_time=ceil(epoch).".".mvindex(split(epoch,"."),1)
| eval time_args = if( -1 * my_local_TZ >= 0, "+".substr(my_local_TZ,2,3), printf("%03d",-1 * my_local_TZ))
| eval UTC = ceil(relative_time(epoch,time_args."h")).".".mvindex(split(epoch,"."),1)
| convert ctime(UTC) ctime(my_local_time)

I'm sorry. I've some mistake. I fixed it.

[weidertc]

I tested this out in multiple timezones and it produces the correct and same answer.

it's odd how it takes all this, but it works.

Thank you!

[richgalloway]

Splunk does not have a function for converting time zones.  Go to https://ideas.splunk.com to suggest one or to up-vote someone else's idea.

Splunk stores times in UTC and then renders them in the user's selected zone.  I suggest you change your Splunk preferences to display time in UTC so you see the true time of the event.