Hi everyone, i have a problem concerning barchart and timestamp
I’m using grafana v13.0.1+security-01,
I use MariaDB as source (with mysql)
I need barchart panel
My problem is when i use “the last …” for display, If i select manualy the day i see value, if i put “the last 6 hours” I dont see anything
When i manualy put the time with the calendar :
When i put “the last X anything” (exemple with 6hours) :
Here my query :
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘+02:00’, ‘+0:00’)) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM
plcnext.opcua
WHERE
$__timeFilter(`timestamp`)
AND (
V_temps_P1 IS NOT NULL
OR V_temps_P2 IS NOT NULL
)
GROUP BY
$__timeGroup(`timestamp`, ‘1h’)
ORDER BY
$__timeGroup(`timestamp`, ‘1h’)
Mofify UTC give me same thing
Can anyone explain me why it append, and if its possible to resolve this
Thanks you
This is a timezone mismatch issue between your stored timestamps and Grafana’s time filter.
$__timeFilter() sends time boundaries in UTC but your timestamps are stored in local time (+02:00):
Manual date → works
“Last 6 hours” → no results
Replace $__timeFilter with Unix epoch comparison
From:
sql
WHERE $__timeFilter(`timestamp`)
To:
sql
WHERE UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, '+02:00', '+00:00'))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
Full Query:
sql
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, '+02:00', '+00:00')) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM plcnext.opcua
WHERE
UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, '+02:00', '+00:00'))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
AND (V_temps_P1 IS NOT NULL OR V_temps_P2 IS NOT NULL)
GROUP BY $__timeGroup(`timestamp`, '1h')
ORDER BY $__timeGroup(`timestamp`, '1h')
Fix Datasource Session Timezone
To ensure $__timeGroup hourly grouping also aligns correctly:
Connections → Data sources → your MariaDB datasource
Scroll to Additional settings
Set Session Timezone→ +00:00
Click Save & test
If hourly grouping still appears off by 2 hours, replace GROUP BY with:
sql
GROUP BY FLOOR(UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, '+02:00', '+00:00')) / 3600)
This hardcoded time shift of +2 migĥt break when time comes to daylight savings time
What time zone are you in? Central europe?
france, UTC+2, inside my db value are at the correct timestamp
What do you mean when you say they are correct value?
Timestamps could be correct but not utc
Try
CONVERT_TZ(your_datetime_column, ‘Europe/Paris’, ‘UTC’) AS utc_time;
Clocks in France change twice a year, shifting on the last Sundays of March and October.
when i say correct i mean there is not miss match between wath expected and the db.
But only the shift when i use “the last …” in grafana, wich became good with : UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘+02:00’, ‘+00:00’))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
That could break in October when you reset your clock.
Use
CONVERT_TZ(your_datetime_column, 'Europe/Paris', 'UTC')
my query is : SELECT
UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘+02:00’, ‘+0:00’)) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM plcnext.opcua
WHERE UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, ‘+02:00’, ‘+00:00’))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
AND (
V_temps_P1 IS NOT NULL
OR V_temps_P2 IS NOT NULL
)
GROUP BY $__timeGroup(`timestamp`, ‘1h’)
ORDER BY $__timeGroup(`timestamp`, ‘1h’)
where should i put the line you gave me ?
Everywhere you are using
CONVERT_TZ(`timestamp`, ‘+02:00’, ‘+00:00’)
i have this error
i try with ’ , it dont give me error but no data message and ` but error
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(timestamp,‘Europe/Paris’, ‘UTC’)) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM plcnext.opcua
WHERE UNIX_TIMESTAMP(CONVERT_TZ(timestamp,‘Europe/Paris’, ‘UTC’))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
AND (
V_temps_P1 IS NOT NULL
OR V_temps_P2 IS NOT NULL
)
GROUP BY $__timeGroup(`timestamp`, ‘1h’)
ORDER BY $__timeGroup(`timestamp`, ‘1h’)
change this setting to say UTC
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC')) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM plcnext.opcua
WHERE UNIX_TIMESTAMP(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC'))
BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
AND (
V_temps_P1 IS NOT NULL
OR V_temps_P2 IS NOT NULL
)
GROUP BY $__timeGroup(timestamp, '1h')
ORDER BY $__timeGroup(timestamp, '1h')
I think the forum is messing up the single quotes.
even when i copy the query you give me i have a no data message,
And apparently it cant detected anymore my time column
translate as “No data available”
oops sorry that was not your original query. try this
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC')) * 1000 AS time,
SUM(V_temps_P1) AS P1,
SUM(V_temps_P2) AS P2
FROM plcnext.opcua
WHERE $__timeFilter(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC'))
AND (
V_temps_P1 IS NOT NULL
OR V_temps_P2 IS NOT NULL
)
GROUP BY $__timeGroup(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC'), '1h')
ORDER BY
$__timeGroup(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC'), '1h')
Also why are you doing the following?
UNIX_TIMESTAMP(CONVERT_TZ(timestamp,'Europe/Paris', 'UTC')) * 1000 AS time
If i don’t use a Convert_TZ grafana shift my data by 2h, for exemple if in my db its at 10h00 utc+2 (wich is good), grafana will make it 12h00 utc+2 (wich is not good) , the first solution was to be in UTC when i select time zone in grafana but not a good methode in my case; so i use convert_tz.
After trying your solution i still have an error
i don’t know why but it don’t understand the ‘Europe/Paris’ Time zone
