Splunk Search

Convert the timezone from a time field

lain179
Communicator

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.

Tags (1)
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

kristian_kolb
Ultra Champion

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

kristian_kolb
Ultra Champion

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.

lain179
Communicator

Ok. Thanks.

0 Karma

bmacias84
Champion

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.

lain179
Communicator

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.

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...