topic Timezone offset causing blanks during conversion in Splunk Search

Timezone offset causing blanks during conversion

rhilderbrand1 — Mon, 06 Dec 2021 20:14:41 GMT

I have a date column that I'm trying to convert to %m/%d/%Y. The date stamp is a little complex but I got it to work until daylight savings took affect. Now anything with a timezone offset that has a non-zero number in the third digit, -0480 for example, returns blank. Below is my query...

Is there something I'm missing?

Re: Timezone offset causing blanks during conversion

johnhuang — Mon, 06 Dec 2021 20:58:58 GMT

Share some examples.

Re: Timezone offset causing blanks during conversion

PickleRick — Mon, 06 Dec 2021 21:04:43 GMT

-0480? What kind of a timezone spec is that?

Re: Timezone offset causing blanks during conversion

rhilderbrand1 — Thu, 09 Dec 2021 14:18:59 GMT

Not sure. Everything was fine during daylight savings time but on November 8, half of the devices started reporting their UTC time offsets in what I assume are minutes rather than hours and minutes. The messed up time offsets are all multiples of 60, for example -0480 or -0360. I suspect the use of %z is strictly with hour and minute formats so anything greater than 59 in the last two digits may throw it off. I guess I'll have to read up on rex or regex to pull the needed info out instead.

Re: Timezone offset causing blanks during conversion

PickleRick — Thu, 09 Dec 2021 20:55:09 GMT

LOL. I just had a very similar case myself. See this thread https://community.splunk.com/t5/Getting-Data-In/Index-time-extraction-and-non-indexed-field/m-p/577773

The difference is that you want to do the parsing/correction in search-time whereas I did it in index-time (since I had to correct _time).

But the general idea is the same - extract the main part of the event as UTC, extract the "timezone" part, cast it to number, multiply by 60 and add the resulting number to the main timestamp.