topic Re: Timezone and Timestamp modification at search/report time? in Getting Data In

Timezone and Timestamp modification at search/report time?

paymon — Thu, 01 Jul 2010 09:13:17 GMT

We have standardized our infrastructure on UTC, but we want to generate reports in PST. Is there a way to specify a timezone transform at search time such that the events themselves don’t need to be modified? the link below would change the actual messages as they come in and I want to keep the data in splunk and my various raw syslog messages consistent.

http://www.splunk.com/base/Documentation/4.1.3/Admin/Applytimezoneoffsetstotimestamps

Re: Timezone and Timestamp modification at search/report time?

Jeremiah — Thu, 01 Jul 2010 14:01:15 GMT

Try using eval to subtract the difference between UTC and PST (in seconds).

| eval _time=_time-28800

Re: Timezone and Timestamp modification at search/report time?

kkalmbach — Sat, 03 Jul 2010 02:39:58 GMT

I too am attempting to view a report and have the times show up in a different time zone.

Your solution simply changes the time, then displays this incorrect time in the "local" timezone so it appears correct.

This does not seem correct to me.

(Using an American example) Let's say something happened at noon eastern time (-4:00). This event correctly gets logged as happening at noon eastern, then I display it using my splunk server in the mountain time zone, it shows up as happening at 10:00 (which is correct).

If I use your approach, I change the time so that the report thinks it happened at 14:00 eastern, then when that time is displayed for the mountain time zone, it shows up as 12:00.

Although the string "12:00" is what I want, this seems a wrong way to do it. This breaks if I decide to print out the timezone as well as the time. It would then show up as "12:00 MST". This also would be fragile if I started to correlate the times with anything else.

What I would like is a way to say I want this report to show up in eastern time (even though the splunk server is in the mountain time zone), then all times would show up in the correct format no matter what.

My other question is when I view a report on splunk (using the web interface), how does splunk decide what timezone to use for displaying the data, does it use the TZ environment variable of the splunk user on the server? Does it use something in the browser (locale)? Something else? Can this be overwritten per user?

-Kevin

Re: Timezone and Timestamp modification at search/report time?

paymon — Mon, 05 Jul 2010 03:35:29 GMT

Kevin,

I am happy enough with Jeremiah's solution because the report is meant to be consumed by humans at meetings and I doubt anyone would care. That said, I am sure that some other use cases out there would not find this solution satisfactory and you are correct. If you find a proper solution please let us all know. Thanks.

Re: Timezone and Timestamp modification at search/report time?

gkanapathy — Sat, 31 Jul 2010 04:53:01 GMT

You need to, at index time, set the time zone of your incoming data so that Splunk knows what the actual real event time is. If unspecified, Splunk assumes it is the same time zone as the Splunk indexer.

When reporting, it will then display and normalize times to the time zone of the Splunk server. Unfortunately there isn't a simple way to change your user's current display time zone. However, it is possible to set the time zone of a search head to something different from the indexer, and have all reports run from that search head, but use distributed search to query the indexer. The reports will be be displayed in the search head time zone, correctly adjusted regardless of the indexer's time zone. So by utilizing multiple search head instances you can have the data displayed in multiple zones.

Re: Timezone and Timestamp modification at search/report time?

rgustafson — Wed, 11 Jul 2012 17:30:15 GMT

Could you handle daylight savings time dynamically?

Re: Timezone and Timestamp modification at search/report time?

reickwort — Thu, 21 Feb 2013 17:57:04 GMT

I know this question is old, but it has 27K views, and no verified answer, and is the top SplunkBase result when I search "change timezone at search time," so it seems like it's still relevant.

I had this problem as well; I have events that contain multiple time fields, and sometimes I want to sort those events based on, or otherwise display, time fields that are NOT the event's timestamp, and show those times in local time for human consumption. To be clear, my log's indexed timestamps are processed correctly, but each event additionally has three more date-time fields that are input in UTC, and I would like to output them in local time.

After searching for a long time through SplunkBase and the documentation with no results, I believe I've figured out a solution on my own.

Assuming original time field origtime is in format %Y-%m-%d %T and is in UTC (but has no timezone notation in the original string):

| replace * with "* UTC" in origtime | eval newctime = strptime(origtime,"%Y-%m-%d %T %Z") | eval newstrtime = strftime(newctime,"%Y-%m-%d %T")

If your original timestamp fields are in some other timezone, just change "UTC" to whatever your timezone value is.

Output (reports) can now use newstrtime as human-readable localized time; for machine-readable purposes (like sorting or timecharts) you can use newctime.

Maybe better Splunkers can make a more elegant set of commands, but this appears to work for me.

Re: Timezone and Timestamp modification at search/report time?

markmcd — Thu, 13 Jun 2013 03:32:32 GMT

This doesn't seem to be true any more. When I set my personal timezone to say UTC+8, when the Splunk data is stored in UTC, everything is adjusted to my local time.

Re: Timezone and Timestamp modification at search/report time?

delink — Mon, 29 Dec 2014 19:21:16 GMT

This only helps for the _time field. What about times stored in other fields?

Re: Timezone and Timestamp modification at search/report time?

bschandramohan — Mon, 28 Sep 2020 19:15:06 GMT

Am i missing something? PST = -7:00 of GMT, so this should be 7*60*60 = 25200. Why is it 28800?

Re: Timezone and Timestamp modification at search/report time?

daviduslan — Mon, 09 Nov 2015 22:42:12 GMT

Pacific Time (PT) is -7 OR -8 of GMT depending on Daylight Saving Time. From winter to spring it's -8 (PST), from spring to winter it's -7 (PDT).

Re: Timezone and Timestamp modification at search/report time?

dajomas — Thu, 27 Oct 2016 10:48:57 GMT

It has been 6 years since this question was asked but since I ran into this issue myself and found a solution that seems to work fine, I still would like to share it with the community.

| eval time_offset = strftime(_time,"%z")
| rex field=time_offset "(?<direction>[+-])(?<hour_offset>\d{2})\d{2}"
| eval date_hour = (date_hour + (hour_offset * if(direction=="-",-1,1))

Line 1 extracts the difference from UTC (ie. for CET it would result in +0200)
Line 2 extracts the +/- and the first 2 digits
Line 3 calculates the actual hour from the event's hour

Re: Timezone and Timestamp modification at search/report time?

dajomas — Fri, 28 Oct 2016 05:34:18 GMT

There was an error in line 3

| eval date_hour = (date_hour + hour_offset) * if(direction=="-",-1,1)

should have been

| eval date_hour = (date_hour + (hour_offset * if(direction=="-",-1,1))

Re: Timezone and Timestamp modification at search/report time?

DalJeanis — Mon, 24 Apr 2017 22:35:38 GMT

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 local, 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 local time zone at the end. Set the values of myDateFormat and myTzName as desired.

The 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.

I also posted this solution here ... https://answers.splunk.com/answers/523905/is-there-a-way-to-show-local-time-of-the-device-of.html

Re: Timezone and Timestamp modification at search/report time?

biswabhusan — Thu, 03 May 2018 11:44:37 GMT

Hi dajomas, will these exressions be run on on the search head?

Re: Timezone and Timestamp modification at search/report time?

dajomas — Fri, 04 May 2018 13:05:06 GMT

@biswabhusan yes, you run this on the search head

Re: Timezone and Timestamp modification at search/report time?

landen99 — Mon, 17 Dec 2018 15:46:01 GMT

I downvoted this post because epoch time!

Re: Timezone and Timestamp modification at search/report time?

Jeremiah — Fri, 11 Jan 2019 15:59:02 GMT

9 years later?

Re: Timezone and Timestamp modification at search/report time?

DEAD_BEEF — Thu, 31 Jan 2019 19:39:44 GMT

I downvoted this post because does not contribute to the content of the post at all.

Re: Timezone and Timestamp modification at search/report time?

landen99 — Sat, 02 Feb 2019 18:26:08 GMT

The answer is as wrong now as it was 9 years ago. Epoch time exists independent of time zones, but your answer alters epoch time without consideration for what epoch time is. No sense continuing the nine years of confusion simply because no one noticed the mistake before.