Custom annotations MySQL query: timeEnd is not correctly interpreted

Grafana MySQL
,
1

I have composed MySQL annotations query which returns both time and timeEnd columns of the same type. Unfortunately it seems that Grafana interprets timeEnd differently from time, basically, fails to convert it internally to timestamp:

image
image1118×862 45 KB

Here is how the event looks like when I run the query manually from CLI:

SELECT prev_last_updated_ts AS time, last_updated_ts AS timeEnd, 'Charging' AS text FROM ...
+-------------------+-------------------+----------+                                                                                        
| time              | timeEnd           | text     |                                                                                        
+-------------------+-------------------+----------+                                                                                        
| 1734106799.903517 | 1734110399.611093 | Charging |                                                                                        
+-------------------+-------------------+----------+

and that is how it looks like if I apply FROM_UNIXTIME() (just for testing purposes):

SELECT FROM_UNIXTIME(prev_last_updated_ts) AS time, FROM_UNIXTIME(last_updated_ts) AS timeEnd, 'Charging' AS text FROM ...
+----------------------------+----------------------------+----------+
| time                       | timeEnd                    | text     |
+----------------------------+----------------------------+----------+
| 2024-12-13 17:19:59.903517 | 2024-12-13 18:19:59.611093 | Charging |
+----------------------------+----------------------------+----------+

Also I think there should be a dummy check that timeEnd ≥ time (show a warning?).

2

try:

  • round it to integer only
  • format it with FROM_UNIXTIME
  • format it as timeries, not table
3

Rounding to int didn’t work, but applying FROM_UNIXTIME() worked just fine, thanks for hint.

image
image397×244 7.99 KB

However you might agree that processing of timeEnd and time should be the same / be consistent?

4

What is in the documentation?

5

On the top of the documentation I found here, there are few tips provided by Grafana in the dialog which configures the annotation query. Nothing special there.

6

Ok, then it makes sense to create feature request. I guess time field has special handling atm, because it’s used for timeseries.

8

I have created issue #98099 (just for reference).
P.S. @jangaraj Слава Україні!