Time series graph shows aggregated values in week, month, year

Grafana
1

Hi just switched from Metabase to Grafana and i have a problem with my Time Series graph. With the following psql query:

SELECT
service.name AS package,
COUNT(*) AS service_on,
DATE_TRUNC(‘${time_variable}’, account.created_shot) AS time
FROM
account
LEFT JOIN
customer c ON c.id_account = account.id
RIGHT JOIN
property p ON p.id_customer = c.id
RIGHT JOIN
service ON service.id_property = p.id
WHERE
service.category = ‘test’
AND service.name IN ($pacchettotest)
– AND account.created_shot BETWEEN ‘2023-11-18’ AND ‘2023-11-21’
GROUP BY
service.name,
DATE_TRUNC(‘${time_variable}’, account.created_shot)
ORDER BY
time, service.name;

I obtain this graph if i select the “day” value of the variable ${time_variable}

Screenshot from 2024-08-06 08-09-37
Screenshot from 2024-08-06 08-09-371086×447 40 KB

But if i switch to “month” the graph aggregates each day of the month into the first resulting in wrong data

Screenshot from 2024-08-06 08-12-52
Screenshot from 2024-08-06 08-12-52974×532 46.5 KB

What i want to achieve is to have on each time occurrence ( day,week,month,year ) to show only the value of the first day of each time range.

It’s probably an issue with the DATE_TRUNC but if you guys have any solution i would be very happy.

Have a nice day

2

click on query inspector when looking for month aggregation, what do you see?

3

I see:

SELECT
service.name AS package,
COUNT(*) AS service_on,
DATE_TRUNC(‘month’, account.created_shot) AS time
FROM
account
LEFT JOIN
customer c ON c.id_account = account.id
RIGHT JOIN
property p ON p.id_customer = c.id
RIGHT JOIN
service ON service.id_property = p.id
WHERE
service.category = ‘test’
AND service.name IN (list of services)
– AND account.created_shot BETWEEN ‘2023-11-18’ AND ‘2023-11-21’
GROUP BY
service.name,
DATE_TRUNC(‘month’, account.created_shot)
ORDER BY
time, service.name;

4

now what do you see when you run that query outside of grafana? a query tool for the database type

5

Sorry for the late reply but i’ve found a working solution:

AND (
‘${time_variable}’ NOT IN (‘month’, ‘week’, ‘year’)
OR (
‘${time_variable}’ = ‘month’ AND DATE_PART(‘day’, account.created_shot) = 1
)
OR (
‘${time_variable}’ = ‘week’ AND DATE_PART(‘dow’, account.created_shot) = 1
)
OR (
‘${time_variable}’ = ‘year’ AND DATE_PART(‘doy’, account.created_shot) = 1
)
)

1 Like