Postgres $__timeGroup macro returns "Found no column named time"

Grafana Prometheus
1

What Grafana version are you using?
5.2.0-pre1

What datasource are you using?
postgresql

What OS are you running grafana on?
centos 7.0

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

Where am I wrong?

2

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.

3

‘23h’ doesn’t work for me, it shows the same error message “Found no column named time”.

4

“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?

5

@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.

6

The generated query looks good, i meant if you can replace the query in grafana and use the evaluated one instead of the one with macros.

7

@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”

And it works.

2 Likes
8

So the problem is that you need to quote time in group by and order by?

10

@mefraimsson Yes. The SQL should be written in that manner with my PostgreSQL

1 Like
11

@anriqing are you sure the quotes are the reason? they shouldnt be required.

12

@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.

13

This work for me as well.