Elapsed time using UTC, dashboard and database using America/New_York

[youzer-name]

I am trying to use a tooltip showing the elapsed time, but the elapsed times are all showing '5 hours ago' for records that are a few minutes old. The datetime field that I am using for the tooltip is stored in the America/New_York timezone which is currently UTC -5.

The dashboard has America/New_York set as the default timezone. It appears that the panel is comparing the datetime field to UTC rather than the local timezone. I can't find anywhere in the panel configuration to change this.

I used convert_TZ(datetime_field, "America/New_York", UTC) as "time" in my query and that seems to have sorted it, but I have to use a second field in my query to return the local time. Should the plugin always be using UTC or should it be aware of the default timezone of the dashboard when calculating elapsed time?

[Dalvany]

Hello @youzer-name thanks for reporting, I'm using grafana's function to convert time to convert time in a "time ago" format. I might have to do some conversion before. I'll try to see if I can do something.
Can you tell me what version of grafana your using and can you give a sample of your time series database result so that I can reproduce ?

[youzer-name]

I am using Grafana v9.1.2 with a MySQL datasource for this panel, not a time-series datasource, so maybe that is a factor.

When I run this:
SELECT filedatetime, convert_tz(filedatetime, "UTC", "America/New_York") as "utc_time" FROM table;

I get results that look like this:

2023-01-20 16:51:09 | 2023-01-20 21:51:09

[Dalvany]

I was looking if grafana offers some date conversion method and I come onto this : grafana/grafana#27535

MySQL datasource have a parameter to set timezone, do you think it will do the trick ?

[youzer-name]

I tried setting that session timezone in the connection to America/New_York, which I think was the existing default in Grafana, and to UTC. Neither had any effect on the displayed elapsed time. The database itself is set to America/New_York. "SELECT now()" returns the local time.

There is also a timezone setting in the dashboard itself. If I set that one to UTC, then the "today" time range doesn't return the correct records for today in local time, so that needs to be kept as the local tz.

I did find that instead of converting to UTC, I can use ' unix_timestamp(filedatetime) as "time" ' and that will also give me the date and elapsed time in the tooltip.

So rather than changing anything about the panel, it may be the best practice is to always use a unix timestamp or UTC time field if you want to use the "include date" feature.

[Dalvany]

Ok, if it's ok with you I'll close the issue and add a note in the README that link to this thread if someone stumble into this problem.

[youzer-name]

Sounds good. Thanks for looking into it.

…

On Wed, Mar 1, 2023 at 2:45 PM Dalvany ***@***.***> wrote: Ok, if it's ok with you I'll close the issue and add a note in the README that link to this thread if someone stumble into this problem. — Reply to this email directly, view it on GitHub <#74 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANTHLHOE7RKH27TQLHEXT6TWZ6RNNANCNFSM6AAAAAAVKYBZNU> . You are receiving this because you were mentioned.Message ID: ***@***.***>