Canvas time formats

I'm having trouble with some time formats.
The time, I collect from ES ("timetal") is in UTC+02, but when I try to format it to my local time, it seems to subtract 2 hours instead of adding them.

My SQL query is as follows:
SELECT HISTOGRAM(datetime_, INTERVAL 1 HOUR) as timetal, COUNT(reference_) as logins FROM "*canvas" GROUP BY timetal

My canvas expession is as follows:
filters
| essql
query="SELECT HISTOGRAM(datetime_, INTERVAL 1 HOUR) as timetal, COUNT(reference_) as logins FROM "*canvas" GROUP BY timetal"
| alterColumn column="timetal" type="date"
| mapColumn name="formatted_timetal" fn={getCell "timetal" | formatdate "D/M HH:mm"}
| table
| render

And my output looks like this:

My desired output is taking the raw time from "timetal" and adding the two hours, so I get a cleaner view in "formatted_timetal".
So for the first row "2019-07-16T08:00:00+02:00" should map to "16/7 10:00".

SImply adding two hours manually will not work, as we will publish this to a customer and can't update the additions with daylight savings.

Does anyone have any thoughts on this? :slight_smile:
I have not gotten closer to a solution.

Hi @_Louw,

Unfortunately the one workaround you mention is the best way at the moment: Adding 2 hours to offset from UTC. This is a known issue and on our roadmap to fix, in the future Canvas should respect the local timezone automatically.

Thanks you :slight_smile:

If it helps you in you debugging, @tims, I discovered that I had been reading the dates wrong, and the time in the left column is the right time - already with the two hours added, so the real issue is with the "formatdate" function in the expression editor, where the added hours are not being taken into account.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.