Wrong Canvas timezone

[dgonzalezp]

I have created a line graph in canvas but in the time line I got a wrong timezone with the next SQL query:

SELECT HISTOGRAM("@timestamp", INTERVAL 1 MINUTES) Hora, ip_origen, COUNT(ip_origen) as Cont
FROM autenticacion WHERE "@timestamp" > NOW() - INTERVAL 10 MINUTES
AND ip_origen IS NOT NULL GROUP BY ip_origen, Hora
ORDER BY Hora

The preview is this:

As you can see the correct hour is 13:00 but in the graph there are 2 hours less:

I would like to know if there is a way to change the time zone, I have tried including tz="UTC+2" in the expression editor with no luck and I have readed something about moment.js but honestly I dont know what that is and I didnt find any tutorial.

Help me please!

[tims (Tim Schnell)]

Hey @dgonzalezp,

Unfortunately I think this is related to a bug we are tracking where Canvas isn't respecting Kibana settings for time zones. Here's the issue in github:

Issue: [Canvas] Display timestamp in the browser's timezone

[Catherine_Liu]

In the interim while we're working on timezone support in Canvas, you can manually offset the time using this expression:

SELECT HISTOGRAM("@timestamp", INTERVAL 1 MINUTES) Hora, ip_origen, COUNT(ip_origen) as Cont
FROM autenticacion WHERE "@timestamp" > NOW() - INTERVAL 10 MINUTES
AND ip_origen IS NOT NULL GROUP BY ip_origen, Hora
ORDER BY Hora
| mapColumn "Hora" fn={getCell "Hora" | math "value + 1000*60*60*2"}
| alterColumn "Hora" type="date"

[dgonzalezp]

Thanks for reply Catherine.
I used the expression but I dont know why I recieved the next error message:

This is my expression editor code:

filters
| essql
query="SELECT HISTOGRAM(pruebafecha, INTERVAL 1 MINUTES) Hora, ip_origen, COUNT(ip_origen) as Cont
FROM autenticacion WHERE pruebafecha > NOW() - INTERVAL 10 MINUTES
AND ip_origen IS NOT NULL GROUP BY ip_origen, Hora
ORDER BY Hora"
| pointseries x="Hora" y="mean(Cont)" color="ip_origen"
| plot defaultStyle={seriesStyle lines=3} yaxis=true
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="left" color="#444444" weight="normal" underline=false italic=false} xaxis=true
| render
| mapColumn "Hora" fn={getCell "Hora" | math "value + 10006060*2"}
| alterColumn "Hora" type="date"

[tims (Tim Schnell)]

@dgonzalezp The "| render" function should be after the bottom two expressions.

[dgonzalezp]

Thanks for reply Tims

I did but still not working with the same error message:

"[mapColumn] > Can not cast 'render' to any of 'datatable'"

[dgonzalezp]

I have added 2 hours manually in the SQL query like this:

SELECT HISTOGRAM(pruebafecha + INTERVAL 2 HOURS, INTERVAL 1 MINUTES) AS Hora, ip_origen, COUNT(ip_origen) as Cont
FROM autenticacion WHERE pruebafecha > NOW() - INTERVAL 1 HOURS
AND ip_origen IS NOT NULL GROUP BY ip_origen, Hora
ORDER BY Hora

It works but I dont know if this is the correct way to do it...

Thank you everyone for the help.

[tims (Tim Schnell)]

Thanks for sharing your solution @dgonzalezp, yeah it looks like you are adding the 2 hours during the SQL query, versus Catherine's example would do it in the expression language. If it works then it works!