Graph only by days (not hours)

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:


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

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

That totally work! Thanks!

@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