Canvas time formats

Elastic Stack Kibana
1

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:

Capture2
Capture2.PNG780×409 26.1 KB

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.

2

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

3

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.

4

Thanks you :slight_smile:

5

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.