I am facing issues with bar graphs when data source is one of PostGres or Mysql.
This problem persists both in Mysql and Postgresql.
The issue is visualized like this: (the image is intentionally in day interval so that I can show you the problem. Otherwise it still persist when it is aggregated monthly)
This problem exists when I use pure sql like this date_trunc('month', dateColumn) as time or like this ‘unix_timestamp(date_format(dateColumn, ‘%Y-%m-01’)) as time’ and this problem rises when one bar is missing a data point for a metric. There is no way of indicating what to do with missing data point for a metric in specific interval in pure sql. As a solution Grafana has timeGroup and it can be used like this $__timeGroup(dateColumn, '43800', 0). But this form also has issues. It tries to put the center of the bar on the first day of the month which causes the aggregation to happen for instance from 01-21 till 02-19 instead of 02-01 till 02-28.
Is there any way that I can get graph bars aggregated correctly from first day of month till end day of that month with out having a visual mistake?
Hey
I have done that. But as I said above, $__timeGroup does not do the correct aggregation I am looking for.
I want to aggregate values from start of a month until end of a month
But what $__timeGroup offers starts date from around 19th of previous month. And the aggregation the is not from start of the month but it is from 19th, 20th or 21st of the previous month
Like this:
I found one accidental (not good for maintenance) work around.
The trick is while you write your time series column with pure SQL, you write the equivalent of the same as a comment.
Here, look for an example:
SELECT
-- $__timeGroup(date, '43800m', 0) as time,
unix_timestamp(date_format(date, '%Y-%m-01')) as time,
count(*),
status as metrics
FROM table
GROUP BY time, metrics;
This I had it written for Mysql. I hope it also works for Postgres.
