How to handle Daylight Savings Time in Kibana Canvas?

Elastic Stack Kibana
1

I am creating a Canvas with a visualization in Timelion. I am able to get accurate time buckets, and when I look at the datatable the graph is based on, I can see that @timestamp is being stored as local time with the correct offset. However, the x-axis will only display UTC times, and I can't simply use a fixed offset because of DST. My timezone in both Kibana settings and the timelion function is set to my region.

I've been able to hack this in essql by getting DATEPART('tz', @timestamp) and then doing a mapColumn to add this offset to @timestamp. However, my boss would really like a moving average , and it seems either very difficult or impossible to do this in Elasticsearch SQL. I have also tried saved visualisations but if I use a Canvas expression for the timerange, it automatically evaluates to a static timerange (or throws an error when I try to pipe it in).

Has anyone managed to handle this issue, and could I see your solution?

2

Update: I solved this myself in an extremely hacky way. After the timelion function I put in this expression:

 | mapColumn "t" fn={getCell "@timestamp"
  | math {string
            "2*"
            {context}
            "-"
            {formatdate "YYYY-MM-DDTHH:mm:ss" | date {context}
          }}}
| alterColumn "t" type="date"

This takes advantage of Kibana's timezone setting. When I input a string to date it assumes that the date is in my local time (including timezone) and converts it to UTC. But if it's already in UTC, it sets the time back by the required offset, so I can get the offset by subtracting one value from the other. Then I add the offset to @timestamp. The 2* is just a simplication of @timestamp + (@timestamp - (@timestamp - offset)).

3

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