Time Offset when using $__timeGroup

  • What Grafana version and what operating system are you using?

Grafana 7.5.15 on fedora 35

  • What are you trying to achieve?

Display average by hour.

  • How are you trying to achieve it?
    SQL Query
    SELECT
    $__timeGroup(ts_mesure,1h) as time,
    avg(masse) as value
    FROM mesures
    WHERE
    $__timeFilter(ts_mesure) AND
    proprietaire = $proprietaire AND
    numero_ruche = $numero_ruche
    GROUP BY 1
    ORDER BY 1

  • What happened?

The time is UTC. When I use the series directly (without average and $__TimeGroup), display is right.

When I use the SQL Query above, data is displayed like UTC was localtime (-2h offset)

  • What did you expect to happen?
    Have the display like raw series (without) offset.

  • Can you copy/paste the configuration(s) that you are having problems with?
    See above query

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No

  • Did you follow any online instructions? If so, what is the URL?
    How to put group by time series in the format as by Alias

welcome to the :grafana: forum, @pjuhen

there are many threads on here about using those time macros with a SQL data source, and managin UTC issues. I highly recommend you search through the forum for similar topics :+1:

and what data source are you using? mysql?

Hi,

I use Mariadb.

This is a view of the panel :

Above, using all the measures with the request :

SELECT
ts_mesure AS “time”,
masse
FROM mesures
WHERE
$__timeFilter(ts_mesure) AND
numero_ruche = $numero_ruche AND
proprietaire = $proprietaire
ORDER BY ts_mesure

Down, to obtain an average per hour :

SELECT
$__timeGroupAlias(ts_mesure,1h,0),
avg(masse) as poids
FROM mesures
WHERE
$__timeFilter(ts_mesure) AND
proprietaire = $proprietaire AND
numero_ruche = $numero_ruche
GROUP BY 1
ORDER BY 1

The two panels use the same data, with time in UTC.

Above, display is right.

Down, display is not converted from UTC to local.

Thanks,

Regards,

have you tried reproducing this error on Grafana 9? Grafana 7.5 is quite old and the time series panel has undergone a lot of development since then…