What did you do?
my query is as below. It returns “Found no column named time” in grafana.
SELECT
$__timeGroup(“GMT_CREATED”, ‘24h’, 0),
COUNT(ID) as “value”,
REGION_NAME as “metric”
FROM
sub_change_order_do
WHERE
$__timeFilter(“GMT_CREATED”) AND
REGION_NAME IN ($region)
GROUP BY time, “metric”
ORDER BY time
That generates the following sql, and it works fine in my postgresql.
SELECT
(extract(epoch from “GMT_CREATED”)/86400)::bigint*86400 AS time,
COUNT(ID) as “value”,
REGION_NAME as “metric”
FROM
sub_change_order_do
WHERE
“GMT_CREATED” BETWEEN ‘2018-05-30T03:48:52Z’ AND ‘2018-06-13T03:48:52Z’ AND
REGION_NAME IN (‘OpsTools_Delta’)
GROUP BY time, “metric”
ORDER BY time
I too faced the same issue and yet to figure out an answer on how to split the timestamp in per day.
However, when I tried ‘23h’ instead of ‘24h’ in timegroup it showed up results.
“Found no column named time” means grafana didnt detect a column named time in the resultset but the timeGroup macro should always create one so this is weird. Can you use the generated query in grafana and see if that works?
@svenklemm
I have already printed the generated sql in my post, you can check it.
The generated sql worked find in the PostgreSQL, so I’m confused about the error message.
@svenklemm
Thank you for your inspiration. I changeed the sql to the belowing:
SELECT
(extract(epoch from “GMT_CREATED”)/86400)::bigint*86400 AS “time”,
COUNT(ID) as “value”,
REGION_NAME as “metric”
FROM
sub_change_order_do
WHERE
$__timeFilter(“GMT_CREATED”) AND
REGION_NAME IN ($region)
GROUP BY “time”, “metric”
ORDER BY “time”
@svenklemm Normally the quotes should not be required.
I have checked it with our DBA, here is his response: we’re not using the official released PostgreSQL, but a forked one. Our PostgreSQL requires either both quotes in SELECT and GROUP statements, or no quotes in SELECT and GROUP statements.
