I’m trying to use time_bucket() / date_truc() from timescaledb on a column called time in Postgres. But I can’t call the data returned by the function “time” because the real column is already called time. I can’t rename the virtual column created by the function because Grafana expects the output time column to be named time. Is there any solution to this?
SELECT
time_bucket(‘1h’, time) AS “time”,
avg(CASE WHEN (data->>‘Value’)=‘’ THEN 0 ELSE (data->>‘Value’)::numeric END) as “Value”
FROM data
WHERE
site_id = 45
AND
time > $__timeFrom()
AND
time < $__timeTo()
GROUP BY time
ORDER BY time
Above returns wrong data because it is grouped by the actual time column.
SELECT
time_bucket(‘1h’, time) AS “el_tiempo”,
avg(CASE WHEN (data->>‘Value’)=‘’ THEN 0 ELSE (data->>‘Value’)::numeric END) as “Value”
FROM data
WHERE
site_id = 45
AND
time > $__timeFrom()
AND
time < $__timeTo()
GROUP BY el_tiempo
ORDER BY el_tiempo
Above does not work in Grafana because no column named time is returned. The data returned by the generated query is perfect though.