Graph only by days (not hours)

1

I have some data in a mysql data base that is used as datasource. With a query, I get all values of power consumption for each day and I want to show them in a graph, but I did not find how.
With a table panel, I can see this:
image

But I only can show a graph like this:

image
image1666×364 16.5 KB

The graph is displaying hours, not only days.

This is how my query looks like:

SELECT
  last_changed  AS "time",
  SUM(state) AS "state"
FROM states
WHERE
  entity_id = 'sensor.consumo_electrico'
  GROUP BY CAST(last_changed AS DATE)
ORDER BY last_changed

Is it possible to show only one bar per day? I am not sure if by hours is the only way…

Thanks

2

In your Graph you have only one datapoint per date but the time is not the same, you can do something like this:

SELECT
  DATE_FORMAT(last_changed '%Y-%m-%d') AS "time",
  SUM(state) AS "state"
FROM states
WHERE
  entity_id = 'sensor.consumo_electrico'
GROUP BY DATE_FORMAT(last_changed '%Y-%m-%d')
ORDER BY last_changed
1 Like
3

That totally work! Thanks!

4

@danielgonzalez the right format of DATE_FORMAT seems to be DATE_FORMAT(date, format)
so the right query should be

SELECT
  DATE_FORMAT(last_changed, '%Y-%m-%d') AS "time",
  SUM(state) AS "state"
FROM states
WHERE
  entity_id = 'sensor.consumo_electrico'
GROUP BY DATE_FORMAT(last_changed, '%Y-%m-%d')
ORDER BY last_changed

another little note: I change the Format as from Time series to Table (in grafana)

other than that, it works for me too, thanks @danielgonzalez

1 Like
6

Hi, this format that wrong in my query panel, please you can help me? I need a similar panel to this web: Grafana

grafana
grafana1718×775 110 KB

I need that my panel show the colum of the date.
Thanks!!