Getting Data In

Timezone and Timestamp modification at search/report time?

paymon
Engager

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

Tags (2)

junshi
Explorer

This was my spin on things to allow for the switch from daylight savings, we also run this as a macro:

| eval day=strftime(_time, "%j") 
| eval myDateFormat="%Y-%m-%d %H:%M:%S.%3Q" 
| eval TimeDisp=strftime(_time,myDateFormat." %Z") 
| eval myTzName=if(day >= 68,if(day <= 306,"CDT","CST"),"CST")
| eval local_time=strftime(2*_time-strptime(strftime(_time,myDateFormat." ".myTzName),myDateFormat." %Z"),myDateFormat)." ".myTzName
| fields - _time TimeDisp myDateFormat myTzName
0 Karma

koshyk
Super Champion

Expecting the function to come out of box from Splunk itself.

What we have done is created a macro, that needs input of "time" in epoch value (could be _time, or your custom time in epoch)

[convert_time_to_UK_local(1)]
args = epochTime
definition = eval currentFormat="%F %T" 
| eval myTZ="Europe/London" 
| eval time_UKLocal=strftime(2*$epochTime$-strptime(strftime($epochTime$,currentFormat." ".myTZ),currentFormat." %Z"),currentFormat) 
| fields - myTZ,currentFormat

and call this to format reports etc.

so in your search you just to

.... | `convert_time_to_UK_local(_time)`

List of timezone database from Wikipedia
Thanks to DalJenis on the idea

landen99
Motivator

In the _time field, timezones can be changed either with the user's timezone preferences, or with copying _time to another field name and then using eval's strftime. Time stored at the correct epoch time in fields besides _time can be easily displayed in any timezone using eval's strftime. The user timezone preferences govern all time zone displays, so a special user can be created to run searches for particular time zone displays, as desired.

Time not stored in the correct epoch time (bad props TZ configurations) need to be altered to the correct epoch time at search time using eval based on the _time range (if statement) of the data stored incorrectly.

Creating a user to display a bad epoch time in the wrong timezone in order to compensate for the inaccuracy and render the time in the correct timezone (almost like corrective lenses) is a workable solution that is not advised simply because of the issues resulting from incorrectly indexing time with access by other users.

snimesh
Explorer

I simply clicked on my user ID -> preferences and change my time preference to the required time zone. This changed the time in my results. Thank you!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

dajomas
Path Finder

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

biswabhusan
New Member

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

0 Karma

dajomas
Path Finder

@biswabhusan yes, you run this on the search head

0 Karma

dajomas
Path Finder

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))
0 Karma

reickwort
Engager

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.

gkanapathy
Splunk Employee
Splunk Employee

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.

markmcd
Path Finder

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.

delink
Communicator

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

0 Karma

landen99
Motivator

Time stored at the correct epoch time can be easily displayed in any timezone using eval's strftime. Time not stored in the correct epoch time (bad props TZ configurations) need to be stored in the correct epoch time with perhaps some kind of search time calculation altering _time to the correct epoch time based on the _time range of the data stored incorrectly.

0 Karma

kkalmbach
Path Finder

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

paymon
Engager

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.

Jeremiah
Motivator

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

| eval _time=_time-28800

landen99
Motivator

I downvoted this post because epoch time!

DEAD_BEEF
Builder

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

0 Karma

Jeremiah
Motivator

9 years later?