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

[vanheer]

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!

[PickleRick]

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).

[vanheer]

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!

[richgalloway]

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.

[vanheer]

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.