topic Re: Convert the timezone from a time field in Splunk Search

Convert the timezone from a time field

lain179 — Wed, 08 May 2013 21:18:55 GMT

I have a field extracted from log entries, containing time values in GMT. Can I convert the field to PST time? If so, how can I accomplish that?

stats values(SearchUser) as User values(SearchStartDate) as "Start" values(SearchEndDate) as "End" by Project, SearchID

SearchStartDate and SearchEndDate need to be converted to PST.

Re: Convert the timezone from a time field

kristian_kolb — Wed, 08 May 2013 22:07:24 GMT

One way would be to make use of the strptime()/strftime() functions of eval, which will let you convert time from strings, e.g. 2013-05-03 12:23:34 to epoch (which is the time expressed as the number of seconds since midnight Jan 1, 1970). While that might seem odd, it makes addition/subtraction very easy.

So. Let's assume that your timestamps look like above, but for the wrong timezone, you can do like this;

your base search 
| eval start_e = strptime(SearchStartDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchStartDate = strftime(start_e, "%Y-%m-%d %H:%M:%S") 
| eval end_e = strptime(SearchEndDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchEndDate = strftime(end_e, "%Y-%m-%d %H:%M:%S") 
| the rest of your search as shown in your question

The 36000 is just the number of seconds (10 hours) which is the difference between the two timezones. NB. This is just my guessing, I don't remember if it's eight or nine hours diff.

strptime() makes the string into an integer, according to the specification
strftime() turns the number back into a string, according to the specification

Also, note that this will NOT change any data in the event, but just modify how it's presented.

Please see the following for more info;

http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/Commontimeformatvariables
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/CommonEvalFunctions
http://www.strftime.net

Hope this helps,

Kristian

Re: Convert the timezone from a time field

lain179 — Wed, 08 May 2013 22:16:22 GMT

I guess there is no function to convert the time directly instead of doing the Math?

Actually the time values in logs are in UTC. Because of the daylight saving, the difference is sometimes 7 hours and sometimes is 8 hours. So adding a constant number won't work too well.

Re: Convert the timezone from a time field

bmacias84 — Wed, 08 May 2013 22:28:42 GMT

I recommend setting your TZ (timezone) settings for each one of your sources or sourcetypes. Splunk will do the math for you. However this will not work on previously indexed data.

Re: Convert the timezone from a time field

lain179 — Wed, 08 May 2013 23:24:53 GMT

Ok. Thanks.

Re: Convert the timezone from a time field

kristian_kolb — Sun, 12 May 2013 14:30:22 GMT

Well, the TZ setting will AFAIK only work on the timestamp used for indexing purposes, so-to-speak, i.e. not for any other time data inside the events.