CODEWITHSUNDEEP
postgresqltime-seriesgrafanadistinct-on
Wouter
Wouter

Reputation: 487

Grafana PostgreSQL distinct on() with time series

I'm quite new to Grafana and Postgres and could use some help with this. I have a dataset in PostgreSQL with temperature forecasts. Mutiple forecasts are published at various points throughout the day (indicated by dump_date) for the same reference date. Say: at 06:00 today and at 12:00 today a forecast is published for tomorrow (where the time is indicated by start_time). Now I want to visualize the temperature forecast as a time series using Grafana. However, I only want to visualize the latest published forecast (12:00) and not both forecasts. I thought I would use DISTINCT ON() to select only the latest published forecast from this dataset, but somehow with Grafana this is not responding. My code in Grafana is as follows:

SELECT
  $__time(distinct on(t_ID.start_time)),
  concat('Forecast')::text as metric,
  t_ID.value
FROM
  forecast_table t_ID
WHERE
  $__timeFilter(t_ID.start_time)
  and t_ID.start_time >= (current_timestamp - interval '30 minute')
  and t_ID.dump_date >= (current_timestamp - interval '30 minute')
ORDER BY
  t_ID.start_time asc, 
  t_ID.dump_date desc

This is not working however since I get the message: 'syntax error at or near AS'. What should I do?

Upvotes: 0

Views: 735

Answers (1)

Jan Garaj
Jan Garaj

Reputation: 28696

You are using Grafana macro $__time, so your query in the editor:

SELECT
  $__time(distinct on(t_ID.start_time)),

generates SQL:

SELECT
  distinct on(t_ID.start_time AS "time"),

which is incorrect SQL syntax.

I wouldn't use macro. I would write correct SQL directly, e.g.

SELECT
  distinct_on(t_ID.start_time) AS "time",

Also use Generated SQL and Query inspector Grafana features for debugging and query development. Make sure that Grafana generates correct SQL for Postgres.

Upvotes: 2

Related Questions