MySQL query syntax to use dashboard datetime range pulldown-UPDATED

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.

What am I missing?
TIA

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.

image

Results in

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


its currently 7:45 CDT, last data row is 6:45am CST, graph shows 1:45.

If I change back to UTC:


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!)

Ok, this is good, narrowing it down…

Any tricks for converting timezones in the SQL? :smile:

1 Like

convert_tz will be your friend.

search through this forum for the usage of it.

Grafana should think that, and it should be true.

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.

Antony.

2 Likes