Is there a way to show local time of the device of that area?

[kiran331]

Hi

I have a data source with device name and timestamp in UTC and gmtoffset values, I need to show the new field with local timestamp showing the local times of that device, can I do it using Splunk query?

base search... | table READER  time_UTC gmtoffset 

eg:

  READER      time_UTC                     gmtoffset
  CA_123      2016-10-01 06:04:51.0        -300

[DalJeanis]

This really ought to be a primitive in splunk, like an optional third parameter on strftime. Unfortunately, doing it accurately requires an immense infrastructture, such as an implementation of the The IANA/Olson Time Zone Database (see http://stackoverflow.com/tags/timezone/info and/or https://www.iana.org/time-zones for discussion.)

Here's one way:

index=_internal | head 2 | table _time 
| rename COMMENT as "The above just gets a couple of records for demonstration purposes."

| eval myDateFormat="%Y-%m-%d %H:%M:%S.%3Q"
| eval TimeDisp=strftime(_time,myDateFormat." %Z")
| eval myTzName = "PST"
| eval localDisp=strftime(2*_time-strptime(strftime(_time,myDateFormat." ".myTzName),myDateFormat." %Z"),myDateFormat)." ".myTzName

This takes the relevant time UTC, pretends it is PST, then translates it back to UTC. It then applies whatever the difference in seconds was to the original time, then formats the result with the time zone at the end. Set the values of myDateFormat and myTzName as desired.

This method is for display only, not for changing the underlying data. It will get the wrong answer for a couple of hours each year when the calculation is performed across the hours that the time is changing to and from daylight savings. Worse, the method is in essence hardcoding the output expectation for the time zone (PST versus PDT), so the report results will be presenting a time zone that is not actually in effect during half the year.

[richgalloway]

Perhaps this will get you started in the right direction. It does not account for daylight saving/summer time. If you need that then consider a custom command.

... | eval UTCepoch=strptime(time_UTC, "%Y-%m-%d %H:%M:%S.%N")| eval localEpoch=UTCtime + (gmtoffset*60) | eval time_local=strftime(localEpoch, "%Y%m%d %H:%M:%D")