topic Re: How to convert the time in raw data to different timezone? in Splunk Search

How to convert the time in raw data to different timezone?

Veeru — Fri, 10 Jun 2022 14:33:25 GMT

I have the raw data where i need to convert the time in raw data to particular time zone
example:if the time contains emea in it i need to convert to CST time.

his is the 3 conditions of time zone:
when emea => CEST/CST time
when apac => HKT time
when us=>EDT

6/10/22
9:39:00.000 AM

2022-06-10 15:39:00 emea

6/10/22
9:41:56.000 AM

2022-06-10 15:41:56 apac

6/10/22
9:41:56.000 AM

2022-06-10 15:41:56 us

Please help me on the query
Thank you in advance

Re: How to convert the time in raw data to different timezone?

richgalloway — Fri, 10 Jun 2022 15:33:16 GMT

You should be able to use the rex command to convert the regions into time zones then use strptime to parse the timestamps. Once parsed, they'll be in UTC. Splunk will convert them to the user's selected time zone.

| rex mode=sed field=timestamp "s/emea/CEST/" | rex mode=sed field=timestamp "s/apac/HKT/" | rex mode=sed field=timestamp "s/us/EDT/" | eval ts= strptime(timestamp, "%Y-%m-%d %H:%M:%S %Z")

Re: How to convert the time in raw data to different timezone?

Veeru — Mon, 13 Jun 2022 12:57:04 GMT

@richgalloway This not giving us the timestamp field even

My issue is when emea comes it should show us the cst time and as well when apac comes it should show us the hongkong time.

Thank you for replying
veera

Re: How to convert the time in raw data to different timezone?

Veeru — Thu, 07 Jul 2022 15:30:56 GMT

Hello All,

I want to convert the UTC time to 3 different time zones as i mentioned in previous message
can anyone please help me out on this.
Basically when time zone consists of emea in it should be convert to CST time
2022-06-10 15:39:00 emea -> 2022-06-10 10:39:00 CST and viceversa

Thank you in advance
veeru

Re: How to convert the time in raw data to different timezone?

PickleRick — Thu, 07 Jul 2022 20:59:25 GMT

Short of doing very nasty tricks and pretending that the time is in fact a completely different timestamp than it really is you can't display a timestamp in a different timezone than the one your user is configured with.

As simple as that.

I know it's sometimes annoying if you want to be able to - for example - switch from one timezone to another to check what the situation looks like for - for example - another team in your multinational corpo. But time manipulation is sufficiently complicated as it is and _not_ being able to render timestamp in arbitrary timezones (possibly omitting the timezone info from resulting string) saves everyone a lot of headaches.

Re: How to convert the time in raw data to different timezone?

JacekF — Fri, 08 Jul 2022 07:59:34 GMT

I'm not sure what do you mean by us, apac or emea time zones. But if you know what is the time difference between us and EDT, apac and HKT, emea and CST time zones, you can use relative_time function to make the time conversions:

| makeresults | eval timestamp="2022-06-10 15:39:00 emea,2022-06-10 15:41:56 apac,2022-06-10 15:41:56 us" | makemv timestamp delim="," | mvexpand timestamp | rex field=timestamp "\s(?<tz>[\w]+)$" | eval time = strptime(timestamp, "%Y-%m-%d %H:%M:%S") | eval time = case( tz=="us", strftime(relative_time(time, "+2h"), "%m/%e/%Y %I:%M:%S %p")+" EDT", tz=="apac", strftime(relative_time(time, "-2h"), "%m/%e/%Y %I:%M:%S %p")+" HKT", tz=="emea", strftime(relative_time(time, "-6h"), "%m/%e/%Y %I:%M:%S %p")+" CST" )

The values I've provided to relative_time for the time specifier argument, probably do not have much sense. You will have to adjust them to properly convert time zones.

Re: How to convert the time in raw data to different timezone?

PickleRick — Fri, 08 Jul 2022 08:38:08 GMT

That's what I meant by nasty tricks. You're not rendering a given timestamp but you're adjusting the _time to a deliberately wrong value to render it to a wrong value _in your local timezone_ and then you add a predefined string.

Re: How to convert the time in raw data to different timezone?

JacekF — Fri, 08 Jul 2022 08:49:20 GMT

I'm not adjusting _time, just creating a new "time" field with converted time zone. I'm not sure if the question was about changing the value of the _time field.

Re: How to convert the time in raw data to different timezone?

PickleRick — Fri, 08 Jul 2022 09:15:17 GMT

OK, you may not be modifying the _time field value itself. That's not the point. The point is that the value you're calculating the string representation is not equal to the _time value. It's skewed with a predefined offset.

Re: How to convert the time in raw data to different timezone?

JacekF — Fri, 08 Jul 2022 09:27:34 GMT

We don't know how those events look like. Maybe they include multiple time fields and the conversion needs to be done with some filed which was not used for _time calculation. In general I agree that such modifications to the _time field should be avoided.

Re: How to convert the time in raw data to different timezone?

PickleRick — Fri, 08 Jul 2022 09:35:43 GMT

It's not about the events themselves 🙂 _time is _time.

As I understand the "problem" - there is a timestamp - possibly parsed out from the event - which as we know is internally stored as number of seconds since epoch and is completely "timezoneless". And OP wants to render it in different timezone than user's configured timezone. Splunk as such doesn't allow it. The strftime converts given timestamp to a string but the timezone is not-configurable. It's always the one configured for the user "globally". And there is no way around it.

What I meant and what you showed is rendering a completely different value of timestamp to make the string representation appear "correct". It "works" meaning that it should produce expected string values but is prone to cause much confusion and errors later when everyone forgets how it's done, when the daylight saving kicks and so on.

Unfortunately, time calculations can be annoying and timezone differences can be confusing.

I suppose I'd advise @Veeru to keep the time rendered in user's timezone but maybe give for reference some predefined timestamps in other timezones rendered in user's timezone.