Add interval variable to postgresql code

Grafana PostgreSQL
1

Hi, everyone!
I use Grafana v12.1.0 and postgresql data source.

I’m trying to build a time series panel. My code already works but I don’t understand how to add an interval variable.

Here is my sql:

SELECT “property_type”,
ROUND(SUM(“EpScha”/1000) * 100.0 / SUM(SUM(“EpScha”/1000)) OVER (), 2) AS “percent”,
SUM(“EpScha”/1000000) as “total”,
TO_CHAR(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp
FROM presentation.“PortfoliosReportSummary”
WHERE to_timestamp(${__from}/1000)::date <= To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp
AND to_timestamp(${__to}/1000)::date >= To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp
GROUP BY “property_type”, TO_CHAR(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)
ORDER BY “total” desc

Here is how my panel looks like now:

image
image2638×697 16 KB

As you can see I have three bars in three single days now. I want to add an $interval variable with values (day, week, month, quarter, year) and have an ability to change the grouping of data by this variable.

Please, help me to add an variable to my code.

P.S. I’ve already checked the documentation.

2

The way I founded but not the smartest:

case when ‘$Interval’=‘week’ then date_trunc(‘week’,To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp)
when ‘$Interval’=‘month’ then date_trunc(‘month’,To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp)
when ‘$Interval’=‘quarter’ then date_trunc(‘quarter’,To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp)
when ‘$Interval’=‘year’ then date_trunc(‘year’,To_char(“Date”, ‘YYYY-MM-DD HH24:MI:SS’)::timestamp)
end

1 Like