I have a MySQL DB with many rows (1 row per minute of data collected from my weather station, going back a few years).
Im trying to use the pulldown to limit how much the query hits the DB for.
The chronology is saved in the table as separate time and date columns:
20:32:02 2025-06-23
20:31:02 2025-06-23
20:30:02 2025-06-23
…
Grafana 11.0.0
Current Code (updated):
SELECT
cast(concat(date, ’ ', time) as datetime) as time, temp
FROM wx_tbl
WHERE $__timeFilter(concat(date, ’ ', time))
ORDER BY wx_id desc;
Draws a nice graph.
If I set the pulldown to Last 12 hours or less, I get no data.
I only get data when set to 24 hours or later.
I have analyzed your problem. Could you please provide me with some demo data or screenshots related to it so that I can help you better? Or you can try this.
Thinking about it laying in bed last night, I wondered if it may be due to UTC/CDT, but thats only -5 / -6 hours, why anything less than 24…
All data is actually stored in CST, does not adjust for Daylight Stupid Time.
Ill give your suggestion a try here in a bit and see what that does.
Status: 500. Message: db query error: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘::timestamp AS “time”, temp FROM wx_tbl ORDER BY time desc’ at line 2
OK this is weird.
After trying your SQL and reverting (did a DISCARD), now I can run a Last 12 hours and get data, but anything less still returns no data.
It IS due to UTC/CDT !
The “weirdness” above, I had changed the timezone from UTC to CDT, and thats how I got last 12 hours. But when I do that, the graph shows latest data 6 hours behind, even though its current
it shows the actual data time (CST) as in the table.
Grafana must think the table data is native UTC.
So now I need to figure out how to trick Grafana into showing table data as Central time, and then adjust the +/- 1 hour for CDT (which I hope the govt stops doing that silliness this year!)
Timestamps in data sources should always be in UTC and then converted if
required to local time by whatever application (Grafana in this case) displays
the data to humans.
Simple example of why - on the day clocks go back by one hour (summer time to
winter time) there are two instances of every timestamp for one hour, when
expressed in “local time” and there’s no way for something like Grafana to
understand the difference.
