topic Re: Apply date offset for all date fields in Getting Data In

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

vanheer — Tue, 03 Jan 2023 16:12:19 GMT

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!

Re: Apply date offset for all date fields

richgalloway — Tue, 03 Jan 2023 14:32:26 GMT

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.

Re: Apply date offset for all date fields

vanheer — Thu, 05 Jan 2023 07:25:54 GMT

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.

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

PickleRick — Thu, 05 Jan 2023 08:03:02 GMT

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

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

vanheer — Thu, 05 Jan 2023 08:37:40 GMT

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!