topic Re: Timezone conversion function. in Getting Data In

Is there any timezone conversion function in splunk to convert timezone in search string?

AditiKulkarni — Tue, 19 Jul 2022 14:21:19 GMT

Is there any timezone conversion function in splunk to convert timezone in search string?

Re: Timezone conversion function.

woodcock — Mon, 29 Jun 2015 04:21:15 GMT

The 2 functions are strptime and strftime. Here is an example string to convert _time (which is stored as UTC) to EST within a search:

... |  eval EST_time=strptime(strftime(_time,"%m/%d/%Y %H:%M:%S EST"),"%m/%d/%Y %H:%M:%S %Z") | eval date_month=strftime(EST_time,"%m") | eval date_mday=strftime(EST_time,"%d") | eval date_hour=strftime(EST_time,"%H")| stats count by date_month,date_mday,date_hour | sort count desc

Re: Timezone conversion function.

rocketboots_ser — Wed, 12 Oct 2016 03:01:19 GMT

I downvoted this post because this is not correct. this query says "pretend this utc date is actually a est date" which will add 5 hours (rather than subtract). what you want is to say "given this utc date, what would it be in est time"? this is not possible to do simply in splunk.

Re: Timezone conversion function.

rocketboots_ser — Wed, 12 Oct 2016 03:02:34 GMT

Try this answer: https://answers.splunk.com/answering/224136/view.html

Re: Timezone conversion function.

rocketboots_ser — Fri, 14 Oct 2016 00:27:40 GMT

Inspired by the excellent answers provided here, it is possible to convert to an arbitrary timezone provided that timezone always has a fixed UTC offset (i.e. does not support day light savings time).

eval _timezone = "AEST"
| eval _time_AEST = _time 
    - (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . strftime(_time, "%:z"), "%F %Z"))
    + (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . _timezone, "%F %Z"))
| eval time_in_AEST = strftime(_time_AEST, "%F %T " . _timezone)

The above works by first subtracting the UTC offset of the timezone as configured in the users preferences, then adds on the UTC offset of the timezone you configure (in this example AEST, but could be -05:30 or any valid Splunk timezone identifier). The UTC offset of the two timezones in question is calculated by using a reference date (I chose 2000-01-01 arbitrarily) and parsing it twice, first using the UTC timezone and then the queried timezone, and the difference of the two yields the timezone offset.

A couple of things to remember:

in the example, Splunk interprets the _time_AEST variable as seconds since epoch (1970-01-01 00:00:00 UTC), and so technically Splunk is interpreting this as a different 'real world' time -- if you attempt to print the timezone of the date, it will incorrectly report the users configured timezone. Instead, whenever printing this date always include the timezone manually and don't use the %Z timezone formats (as is done in the last line of the example).
I discovered that Splunk Light Version 6.4.1.2 seems to have a bug where it ignores the users timezone and always reports UTC. Using the above method will not break under such circumstances.
The above can easily be converted to a macro. However I have found that when passing arguments to a macro it is best to use pre-calculated fields, rather than expressions (since expressions will have their " quotes stripped when passed as arguments to a macro).
Splunk does not seem to support the tz database - it only seems to support timezones with a fixed offset from UTC. For example, if you live in Sydney you would usually select the "Australia/Sydney" timezone from timezone dropdowns - however this item is not available in Splunks list of timezones in the user preferences. I believe this is because Sydney adheres to Day Light Savings time. If you wish to run a query using local time of an area which is not always a fixed offset from UTC, then you will have to upload a timestamp to Splunk in local time (of course this only applies if you are in control of your input sources) and not have Splunk interpret it (i.e. use a different field other than the _time property). For our use case, we are uploading the 'real world time' using time since Epoch (assigned to the _time property), and additionally upload a timestamp field formatted as a date in local time which Splunk interpets as a string. This gives Splunk enough information to assign the correct time to the event, but also allows us to run queries against the local time where the data is sourced from.

Re: Timezone conversion function.

Richfez — Fri, 14 Oct 2016 01:39:11 GMT

I don't know to what you are referring to, but the answer by @woodcock seems like a fine example of a potential solution to the question as asked.

Re: Timezone conversion function.

woodcock — Fri, 14 Oct 2016 15:55:40 GMT

I don't mind a downvote when I am actually wrong but he has in no way explained that I am. I stand by my answer as-is. Thanks for double-checking @rich7177.

Re: Timezone conversion function.

ppablo — Fri, 14 Oct 2016 16:22:58 GMT

Hi @rocketboots_services

Downvoting users' answers/comments in this forum should be reserved for suggestions that could potentially do harm to someone's environment. People are just trying to help each other out and learn. Simply commenting on @woodcock's answer would have sufficed. Please review how voting etiquette works on Splunk Answers for providing positive, constructive engagement within the community:
https://answers.splunk.com/answers/244111/proper-etiquette-and-timing-for-voting-here-on-ans.html

Re: Timezone conversion function.

rocketboots_ser — Mon, 17 Oct 2016 00:01:33 GMT

Hi @rock7177 and @woodcock, thank you for pointing out that my comment was not fully explained. Apologies for incorrectly using down vote, it will not happen again (unfortunately I can't remove the vote now - "Sorry, but you can't cancel a vote after more than 1 day").

There are two problems with the provided answer: it converts not from UTC to EST, but instead from EST to UTC (the wrong direction), and it does not take the timezone configured in the users preferences into account. An example follows.

index=* | head 1 
| eval time_in = strptime("2016-10-17 00:00:00 UTC", "%F %T %Z")
| eval time_adjusted = strptime(strftime(time_in,"%F %T EST"),"%F %T %Z")
| eval time_in_printed = strftime(time_in, "%F %T %Z")
| eval time_adjusted_printed = strftime(time_adjusted, "%F %T EST")
| table time_in_printed, time_adjusted_printed

Assuming my user preferences are set to UTC timezone, then I get the following results:

time_in_printed time_adjusted_printed
2016-10-17 00:00:00 UTC 2016-10-17 05:00:00 EST

This is incorrect. The correct answer would be 2016-10-16 19:00:00 EST, see https://savvytime.com/converter/utc-to-est/00-00. The problem is that it is replacing the timezone information (+00:00) with EST (-05:00), rather than converting it. However, if my timezone is set to +10:00 Brisbane:

time_in_printed time_adjusted_printed
2016-10-17 10:00:00 AEST    2016-10-18 01:00:00 EST

This is a different but also incorrect answer. This is because the conversion method does not account for the timezone adjustment caused by updating the user preferences to anything other than UTC.

Re: Timezone conversion function.

woodcock — Sat, 25 Nov 2017 02:12:03 GMT

I revisited this and came up with this complete question and answer:
https://answers.splunk.com/answers/590067/how-do-i-map-my-personally-tz-adjusted-time-to-ano.html#answer-590068

Re: Timezone conversion function.

woodcock — Sat, 25 Nov 2017 02:12:39 GMT

I revisited this and came up with this complete question and answer:
https://answers.splunk.com/answers/590067/how-do-i-map-my-personally-tz-adjusted-time-to-ano.html#answer-590068

Re: Timezone conversion function.

EvilPuppetMaste — Fri, 19 Jan 2018 03:22:08 GMT

Improving on the answer above, here is a version that DOES account for daylight savings timezones, as well as times with microseconds:

| makeresults 
| eval t=strptime("2017-01-01 14:00", "%F %H:%M"),
  from_tz="UTC",
  to_tz="Australia/Melbourne",
  from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"),
  to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"),
  offset=round((from_t-to_t)/60/60),
  converted=strftime(t + (from_t-to_t), "%c")

It can be simplified for brevity, I've just broken it into different variables to make it easier to follow. Also the offset variable is unused, but is just there so you can experiment by changing dates and timezones to see that the offset does actually change depending on whether daylight savings is applicable for the date. Note that if you put in explicit DST timezone names like 'AEDT' then it will ALWAYS be daylight savings, regardless of the date. But other timezone names (like Australia/Melbourne used in the example), which are sometimes DST and sometimes not, will change offset according to the input date. The list of timezone names appear to be the standard list from Java.

Re: Timezone conversion function.

jlemley — Wed, 24 Apr 2019 19:57:14 GMT

This is a perfect solution. I modified it slightly for my use case, which is to ensure that the user's timezone matches the timezone of the source data. So the from_tz is done this way:

from_tz=strftime(now(),"%Z")

Re: Timezone conversion function.

Nam7Splnk — Thu, 09 May 2019 13:13:43 GMT

Thanks. This seems to be working great. However, only one issue of DayLight Savings. When converting from EDT to Asia/Shanghai timezone, i get offset of 12 which should be 13 depending on time of the year. any idea how to fix this issue ?

Re: Timezone conversion function.

jlemley — Sat, 11 May 2019 12:57:22 GMT

Try using America/New_York for the origin timezone. This should automatically convert between EDT and EST.

Re: Timezone conversion function.

Ginger_chacha — Tue, 19 Jul 2022 04:33:53 GMT

@EvilPuppetMaste wrote:
Improving on the answer above, here is a version that DOES account for daylight savings timezones, as well as times with microseconds:
| makeresults 
| eval t=strptime("2017-01-01 14:00", "%F %H:%M"),
  from_tz="UTC",
  to_tz="Australia/Melbourne",
  from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"),
  to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"),
  offset=round((from_t-to_t)/60/60),
  converted=strftime(t + (from_t-to_t), "%c")
It can be simplified for brevity, I've just broken it into different variables to make it easier to follow. Also the offset variable is unused, but is just there so you can experiment by changing dates and timezones to see that the offset does actually change depending on whether daylight savings is applicable for the date. Note that if you put in explicit DST timezone names like 'AEDT' then it will ALWAYS be daylight savings, regardless of the date. But other timezone names (like Australia/Melbourne used in the example), which are sometimes DST and sometimes not, will change offset according to the input date. The list of timezone names appear to be the standard list from Java.

This solution is incorrect.

Try below, convert 2022-11-06 01:10 US/Eastern and 2022-11-06 02:10 US/Eastern to Australia/Sydney time, you get 2022-11-06 15:10(Incorrect) and 2022-11-06 18:10(Correct) Sydney time respectively.

The way to calculate the two time zone's time gap is incorrect:

It assumes the gap is 2022-11-06 01:10 US/Eastern and 2022-11:06 01:10 Australia/Sydney.

When it approaches to the clock change hour(i.e. 2022-11-06 02:00 AM) the error happens

| makeresults | eval t=strptime("2022-11-06 01:10", "%F %H:%M"),
from_tz="US/Eastern",
to_tz="Australia/Sydney",
from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"),
to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"),
offset=round((from_t-to_t)/60/60),
converted=strftime(t + (from_t-to_t), "%Y-%m-%d %H:%M")
| append [| makeresults | eval t=strptime("2022-11-06 02:10", "%F %H:%M"),
from_tz="US/Eastern",
to_tz="Australia/Sydney",
from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"),
to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"),
offset=round((from_t-to_t)/60/60),
converted=strftime(t + (from_t-to_t), "%Y-%m-%d %H:%M") ]
| fields converted, from_tz, to_tz | table converted, from_tz, to_tz

Re: Timezone conversion function.

bowesmana — Tue, 19 Jul 2022 06:58:44 GMT

Mmm...

Re: Timezone conversion function.

Ginger_chacha — Fri, 22 Jul 2022 22:56:06 GMT

My profile is US/Eastern TZ based.

I checked for every hour from 2022-01-01 to 2023-01-01 for a few TZs, to ensure all clock changes are covered

1. Most of the MET<->US/Eastern conversion is incorrect

| makeresults | eval t0="2022-04-12 04:04", t=strptime(t0, "%F %H:%M"), from_tz="MET", to_tz="US/Eastern" | eval from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"), to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"), offset=round((from_t-to_t)/60/60), converted=strftime(t + (from_t-to_t), "%Y-%m-%d %H:%M") | append [makeresults| eval t0="2022-04-12 04:04", t=strptime("2022-04-12 04:04", "%F %H:%M"), to_tz="MET", from_tz="US/Eastern" | eval from_t=strptime(strftime(t, "%c.%6N " . from_tz), "%c.%6N %Z"), to_t=strptime(strftime(t, "%c.%6N " . to_tz), "%c.%6N %Z"), offset=round((from_t-to_t)/60/60), converted=strftime(t + (from_t-to_t), "%Y-%m-%d %H:%M") ] | table t0, converted, from_tz, to_tz

Actual Result:

Expected Result:

2022-04-12 04:04 MET = 2022-04-11 22:04:00 US/Eastern
2022-04-12 04:04 US/Eastern = 2022-04-12 10:04:00 MET

I got the total count: 5209 results of 8761 tests were incorrect, compared with the Python converted one.

8761 = 24 * 365 + 1

2. From other regions to US/Eastern, one year there's one hour incorrect

Other regions tested:

Asia/Seoul, Australia/Sydney,Europe/London,Europe/Paris,GB-Eire,GMT,Japan

from	python_to	splunk_to
2022-03-13 02:03:00 GB-Eire	2022-03-12 21:03:00 US/Eastern	2022-03-12 22:03:00 US/Eastern
2022-03-13 02:03:00 GMT	2022-03-12 21:03:00 US/Eastern	2022-03-12 22:03:00 US/Eastern
2022-03-13 02:03:00 Japan	2022-03-12 12:03:00 US/Eastern	2022-03-12 13:03:00 US/Eastern
2022-03-13 02:03:00 Asia/Seoul	2022-03-12 12:03:00 US/Eastern	2022-03-12 13:03:00 US/Eastern
2022-03-13 02:03:00 Europe/London	2022-03-12 21:03:00 US/Eastern	2022-03-12 22:03:00 US/Eastern
2022-03-13 02:03:00 Australia/Sydney	2022-03-12 10:03:00 US/Eastern	2022-03-12 11:03:00 US/Eastern
2022-03-13 02:03:00 Europe/Paris	2022-03-12 20:03:00 US/Eastern	2022-03-12 21:03:00 US/Eastern
2022-04-03 02:04:00 Australia/Sydney	2022-04-02 11:04:00 US/Eastern	2022-04-02 12:04:00 US/Eastern

Use the 2022-03-13 02:03:00 Australia/Sydney as an example, per Time Converter and World Clock - Conversion at a Glance - Pick best time to schedule conference calls, webinars, online meetings and phone calls. (worldtimebuddy.com)

The correct result should be 2022-03-12 10:03:00 US/Eastern instead of 2022-03-12 11:03:00 US/Eastern

3. From US/Eastern to other regions

From Region	To Region	Error Count	Total Test
US/Eastern	Asia/Seoul	27	8761
US/Eastern	Australia/Sydney	60	8761
US/Eastern	Europe/London	19	8761
US/Eastern	Europe/Paris	23	8761
US/Eastern	GB-Eire	19	8761
US/Eastern	GMT	9	8761
US/Eastern	Japan	27	8761

Conclusion:

MET<-> US/Eastern conversion has many errors and can't be used in production

Other Regions->US/Eastern result is acceptable

US/Eastern->Other regions has some errors around the clock change dates. Depends on the requirement it could be used.

Finally - why Splunk doesn't provide native TZ Conversion function is a question.

Re: Timezone conversion function.

PickleRick — Sat, 23 Jul 2022 08:22:27 GMT

I suppose it's "by design". Your user has a defined time zone and splunk renders all times in that zone while internally representing the timestamps as epoch-based unix timestamps.

I know that there are some border-cases (mostly when you're working in some multinational environment and want to have a different timezone based view on something to see how it corresponds to - for example - another country based team work schedule) but for most part it's actually a pretty sound design choice. If people suddenly started casting timestamps into various timezones, you would have no way of telling whether "10:41" means 10:41UTC or 10:41CET.

Re: Timezone conversion function.

carbdb — Tue, 29 Oct 2024 15:24:47 GMT

came in handy here in Germany after Winter-Time Change