Getting Data In

How to change all date fields with the correct time zone?

vanheer
Explorer

I have data with multiple date fields in GMT time. when I import the data with setting the TZ=Europe/Berlin, I see that the _time in the correct time zone but for other date fields are still in GMT time.

 

 

props.conf:
[machine_log]
BREAK_ONLY_BEFORE_DATE=null
CHARSET=UTF-8
FIELD_DELIMITER = ,
FIELD_NAMES = DB_ID, DateOn, DateHist, DateOff, ExportTime, Item, Machine, Section
TIMESTAMP_FIELDS = DateOn, DateHist, DateOff, ExportTime
INDEXED_EXTRACTIONS=csv
KV_MODE=none
LINE_BREAKER=([\r\n]+)
NO_BINARY_CHECK=true
SHOULD_LINEMERGE=false
TZ=Europe/Berlin
category=Structured
disabled=false
pulldown_type=true

 

 

 

I'm still getting data in this way:

_time DB_ID DateOn DateHist DateOff ExportTime Item Machine Section
2023-01-03 12:42:38.787 B0123 2023-01-03 11:41:52.897 2023-01-03 11:42:38.787 2023-01-03 11:42:38.787 2023-01-03 11:42:38.787 I01 M01 S01
2023-01-03 12:41:43.847 B0223 2023-01-03 11:40:18.800 2023-01-03 11:41:43.847 2023-01-03 11:41:43.847 2023-01-03 11:41:43.847 I12 MD1 S02

 

index time in the correct time, but all date fields in the original timing with one hour offset.

The question is: How to change all date fields with the correct time zone?

 

Thanks in advance!

Labels (3)
Tags (3)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

Adding a bit of explanation to @richgalloway 's advice.

If the event's date is parsed from the event, it's converted into a unix timestamp (number of seconds since epoch) and stored as a number within the _time field. By default when the _time field is displayed it's implicitly converted to a string using user's timezone settings so that's why _time is displayed as "2023-01-03 12:42:38.787" in your case (your user's webui TZ setting is GMT+1 I assume so if the timestamp is being parsed with the Europe/Berlin as configured and is displayed in the same timezone, it shows the same value of time).

But the rest of the fields (as well as the field that was used to calculate the _time of the event) are not parsed as timestamps. They are stored as strings so they contain the same raw value that was included in the event and are not processed in any way.

You could create a calculated field which would do a strftime(strptime()) of the original fields and they would show you the timestamps in your local timezone.

The best thing to do however would be to include timezone information in the timestamps themselves because any solution relying on assumed timezone is prone to generating errors if someone from another TZ worked with your data and/or in case of the TZ change (like daylight saving).

0 Karma

vanheer
Explorer

Thanks @PickleRick 

Yes I'm doing that using the calculated fields:

EVAL-DateOff
strftime(strptime(DateOff, "%F %H:%M:%S.%Q") + (strptime(strftime(strptime(DateOn, "%F %H:%M:%S.%Q"), "%c.%6N " . "UTC"), "%c.%6N %Z")-strptime(strftime(strptime(DateOn, "%F %H:%M:%S.%Q"), "%c.%6N " . "Europe/Berlin"), "%c.%6N %Z")), "%Y-%m-%d %H:%M:%S.%Q")

EVAL-DateOn
strftime(strptime(DateOn, "%F %H:%M:%S.%Q") + (strptime(strftime(strptime(DateOn, "%F %H:%M:%S.%Q"), "%c.%6N " . "UTC"), "%c.%6N %Z")-strptime(strftime(strptime(DateOn, "%F %H:%M:%S.%Q"), "%c.%6N " . "Europe/Berlin"), "%c.%6N %Z")), "%Y-%m-%d %H:%M:%S.%Q")

 

Thanks anyway!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Use the strptime() function to convert each date fields into epoch form and strftime() to convert them into a displayable format.  The former will put them into UTC and the latter will put them into the user's selected time zone.

---
If this reply helps you, Karma would be appreciated.

vanheer
Explorer

Thanks @richgalloway 

Actually, I'm using that right now.  I thought that can be done somehow in the props configurations and changing all date-time fields directly from there.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...