Wrong timezone on csv export

[joortwein]

when i export data to csv, the timestamps are shown 2 hours lower than the ones in the gui.
for example the timestamp in the gui is "2023-08-29 20:00:00" but after export it es shown as "2023-08-29 18:00:00"

German timezone is my default. Seems the export routine converts to UTC.
This is very confusing during troubleshooting my datarows

History Adapter is v2.2.6
Data is stored in influxdb 1.8

[Apollon77]

Whats the timezone of yout hostsystem? assumption: it is UTC and not "german summer time" ... fix there and files are fixed too

[joortwein]

No, timezone on the server is CEST and shows exactly the same time as the client.

Influx has the behavior, when changing the time format with "precision rfc3339", all listed dates are shown in UTC.
To display the right time (+2) there must be a command at the end of the query in the form like "tz('CET')"

In a query it looks like the following
select * from "javascript.0.TasmotaVerbrauch.StromZaehler.aktueller_wert" where time <=1693342850000000000 order by desc limit 10 tz('CET')

Possibly the csv function at the history adapter doesn't consider this behavior?

[Apollon77]

Also our influxdb adapter do not have this addition btw

[joortwein]

What I doesn't understand is, that the time in the history gui is ok, but not in the csv data.
Both queries should work over the influxdb adapter,or?

[Apollon77]

That CSV is generated by Admin ... normally the data coming from server are"just" numbers .. so it more seems to be an iadmin issue when? @foxriver76 could you please have a look in admin when it comes to this CSV generation?

[Apollon77]

@joortwein BTW ... if the data is storedf in an influxdb then it is also not history adaoter but the influxdb adater ...

[foxriver76]

" but after export it es shown as "2023-08-29 18:00:00"

is it maybe shown as 2023-08-29 18:00:00Z? And in general the format should be a bit different. Because the export happens as ISO string, so without timezone offset, which is determined by the Z (UTC) and we currently have UTC + 2

ioBroker.admin/src/src/components/Object/ObjectHistoryData.jsx

Line 1330 in 2accc80

new Date(state.ts).toISOString(),

[joortwein]

"is it maybe shown as 2023-08-29 18:00:00Z ?"

Yes, it is!
2023-09-11T12:18:18.178Z

When the datestring is calculatet in the code, it should be a easy doing to consider the clients timezone on this calculation .... I hope

[foxriver76]

Yes we can easily change this to the clients time zone. Do you see any downsides @Apollon77?

[joortwein]

or do a extra switch in the config to dicide to do the csv download with utc or client timezone.
So everybody can choose his disired timezone.
More work for you, but also more flexible

King size solution: provide a drop down field with all available time zones and pre selected client timezone

[Apollon77]

"Downsides" is the potential breaking nature of the change for other users of the csv export.

So the intersting question is now what people do with the CSV and which software tehy use that can not understand ISO timestamps :-)

So @joortwein what you do with the CSV that do not support ISO timestamps?

[mcm1957]

Using an option to select either local (=client) timezone or utc for export looks optimal.
This should avoid a breaking change while adding the possibility to export using local time.

In addition I know the problem of utc vs local time zone from my previous job - whatever you select to use there are complains that for business goal xxx the other solution is required :-)

But I do not know what effort would be to adapt the gui ...

[joortwein]

@Appolon77
simple answer: the display between the dates in the history view and the exportet dates are inconsistent!
This can be very confusing. As user I have the expectation to get what I see!
Not everybody of your users are it-specialists, but they can also overlook this little detail.
Yes, as hard rocking it-nerd, you can change the timestamp like you want. ...but...
Please be user friendly and let the user decide, how he wants this data displayed.

Or, place a hint beneath the export button that the exported data is in utc and for the rest let the user stay alone to convert the data to his local time (not user friendly)

[foxriver76]

I tend to go, to WYSIWYG or making configurable, but I am not sure if anyone needs the UTC export, so maybe we change to WYSIWYG and if someone complains, make it configurable, from my gut feeling this will save us work, because nobody needs the UTC version.

[joortwein]

... and the utc lovers are probably more the data jugglers and can convert the data to their heart's content they want.