Invalid Type for Column Time MySQL

[mfishergsi]

I’m trying to graph the count of different entries in the “Homebase_Name” column. It bar graphs correctly if I use NOW() AS “time”, however I can’t limit the date/time range of course.

I tried specifying the column ‘Date’ AS “time” but Grafana returns an error “invalid type for column time, must be of type timestamp or unix timestamp, got: string Date”

Below is the table and the queries I used.

+---------------+--------+----------+---------------------+
| Homebase_Name | SOP_id | Homebase | Date                |
+---------------+--------+----------+---------------------+
| Off-Base      |      1 |        3 | 2020-01-04 00:00:00 |
| Off-Base      |     20 |        3 | 2020-06-10 00:00:00 |
| TSRT          |     21 |        1 | 2020-06-12 00:00:00 |
| TSRT          |     22 |        1 | 2020-06-12 00:00:00 |
| GRST          |     34 |        2 | 2021-02-10 00:00:00 |
+---------------+--------+----------+---------------------+

SELECT
‘Date’ AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘Off-Base’

SELECT
‘Date’ AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘TSRT’

SELECT
‘Date’ AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘GRST’

[wlargou]

Hi,

Please check this post if relevent :

Timestamp issue MySQL

I have MySQL table having a field as value as int and time as int, and i am storing epoch in the time field in UTC. when i use this table in grafana with mysql datesource it shows me correct output. I am using timezone as UTC then it shows me the correct value Now i want to convert it into IST(UTC+5.30) so I select timezone as Local browser time, still it shows time in UTC. So my question is, can grafana convert time UTC to Local browser time to data in a column. Here my query: select valu…

Hope it helps.

Good Luck

[codlord]

If your SQL is select 'Date' with single quotes then you are saying select a literal string containing the word Date not the Date column. Which is why you are seeing the type error - your result is a string (the word Date).

Just remove the single quotes from your select.

[mfishergsi]

Thanks for the headsup on that! I finally got it working to Bar Graph within a selected range using these queries below.

SELECT
Date_Time AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘Off-Base’ AND $__timeFilter(Date_Time)
GROUP BY time

SELECT
Date_Time AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘TSRT’ AND $__timeFilter(Date_Time)
GROUP BY time

SELECT
Date_Time AS “time”,
Homebase_Name,COUNT(Homebase_Name)
FROM Homebase View
WHERE Homebase_Name = ‘GRST’ AND $__timeFilter(Date_Time)
GROUP BY time