Bar chart broke after upgrade

[enguerranhv]

I recently upgrade my grafana from 13.0.1 to 13.1.1.

And I got 2 trouble when i went on my dashboard.

I use MySQL, it should not be a time zone trouble.

First : I have no more date show, they are in my DB but not show on my panel (translate : No data available)

SELECT

UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘Europe/Berlin’, ‘+0:00’)) * 1000 AS time,

SUM(V_nombre_P1) AS P1,

SUM(V_nombre_P2) AS P2

FROM plcnext.opcua

WHERE UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘Europe/Berlin’, ‘+00:00’))

BETWEEN $__unixEpochFrom() AND $__unixEpochTo()

AND (

V_nombre_P1 IS NOT NULL

OR V_nombre_P2 IS NOT NULL

)

GROUP BY $__timeGroup(`timestamp`, ‘1h’)

ORDER BY $__timeGroup(`timestamp`, ‘1h’)

And the second one is i cant sort my bar chart with the time period select : Before the upgrade, when i select Last 1 hour, the panel only show the last hour, now it show all the data since the start

SELECT

timestamp,

SUM(V_nombre_P1) AS P1,

SUM(V_nombre_P2) AS P2

FROM plcnext.opcua

WHERE (

V_nombre_P1 IS NOT NULL

OR V_nombre_P2 IS NOT NULL

)

GROUP BY $__timeGroup(timestamp, ‘1h’)

ORDER BY $__timeGroup(timestamp, ‘1h’)

[enguerranhv]

It might be the Unix_timestamp, because when I made exactly the same query but without the Unix ( just timestamp AS time ) I got data

[infofcc3]

The second issue is due to the query itself. __$timeGroup() only groups rows into hourly buckets; it doesn’t filter rows by the dashboard time range.

Since your second query doesn’t have a time filter, it will always return data from the entire table regardless of the selected time range.

Add __$timeFilter(timestamp) to the WHERE clause, for example:

SELECT
    timestamp,
    SUM(V_nombre_P1) AS P1,
    SUM(V_nombre_P2) AS P2
FROM plcnext.opcua
WHERE $__timeFilter(timestamp)
  AND (
      V_nombre_P1 IS NOT NULL
      OR V_nombre_P2 IS NOT NULL
  )
GROUP BY $__timeGroup(timestamp, '1h')
ORDER BY $__timeGroup(timestamp, '1h');

For the first issue, I’d recommend checking Panel → Inspect → Query and running the generated SQL directly in MySQL. That will help determine whether the issue is in the generated SQL or elsewhere.

[infofcc3]

Root cause for both: $__timeGroup() expands to
UNIX_TIMESTAMP(timestamp) DIV 3600 * 3600, which doesn’t match the
SELECT expression (raw timestamp or CONVERT_TZ(...)).
MySQL 8’s ONLY_FULL_GROUP_BY rejects that mismatch →

Group in a subquery so SELECT and GROUP BY match exactly →

Query 1

sql

SELECT bucket * 1000 AS time, P1, P2
FROM (
    SELECT UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, 'Europe/Berlin', '+00:00')) DIV 3600 * 3600 AS bucket,
           SUM(V_nombre_P1) AS P1, SUM(V_nombre_P2) AS P2
    FROM plcnext.opcua
    WHERE CONVERT_TZ(`timestamp`, 'Europe/Berlin', '+00:00') BETWEEN $__timeFrom() AND $__timeTo()
      AND (V_nombre_P1 IS NOT NULL OR V_nombre_P2 IS NOT NULL)
    GROUP BY bucket
) t
ORDER BY time

Query 2 (also adds $__timeFilter(), as noted above) →

sql

SELECT bucket * 1000 AS time, P1, P2
FROM (
    SELECT UNIX_TIMESTAMP(`timestamp`) DIV 3600 * 3600 AS bucket,
           SUM(V_nombre_P1) AS P1, SUM(V_nombre_P2) AS P2
    FROM plcnext.opcua
    WHERE $__timeFilter(`timestamp`)
      AND (V_nombre_P1 IS NOT NULL OR V_nombre_P2 IS NOT NULL)
    GROUP BY bucket
) t
ORDER BY time

[kimberlyyerhot63]