Extract a distinct value from a measurement based on day and time? (Utility tier pricing)

Grafana InfluxDB
1
  • What Grafana version and what operating system are you using?
    Grafana v11.6.1 (ae23ead4d9) via docker container and influxdb v1

What are you trying to achieve?

  • Extract a distinct value from a measurement based on day and time. Specifically, the value from pge_hourly_elec_rate_per_kwh from the day before yesterday at 10AM, 3PM, and 6PM. A bonus would be if I could identify weekend vs weekday easily as I see them implementing different rates based on day of week in the future.
    image
    image1354Ă—574 107 KB

How are you trying to achieve it?

  • I have a script that runs once per day to scrape my utility’s website for my rates. The rates are from two days ago as there is a slight lag in the data availability. So, a script that runs on 5/10 populates the utility rates for 24 individual values from 2025-05-08 00:00:00 to 2025-05-08 23:00:00.

  • Using the Day before yesterday timeinterval on the explore view I am able to view a day’s worth of rates.

    image
    image3948Ă—2062 398 KB

What happened?

What did you expect to happen?

  • Have 3 queries that result in the three distinct values for the three different times as relative time.
  • Bonus if there is a way to split this into six values for weekend vs weekday.

Can you copy/paste the configuration(s) that you are having problems with?

  • In Grafana: SELECT distinct("value") FROM "Utility Rate $/kWh" WHERE ("entity_id"::tag = 'pge_hourly_elec_rate_per_kwh') AND now-2d/d GROUP BY time($__interval) fill(null)
  • In Influx: SELECT distinct("value") FROM "Utility Rate $/kWh" WHERE ("entity_id"::tag = 'pge_hourly_elec_rate_per_kwh') AND time >= 1746687600000ms GROUP BY time(1h)
1 Like
2

The “now-2d/d” syntax cant be used in an influx query. If you have a SQL datasource (e.g. postgres), you could make a variable that figures out the date you are interested in and get it in a unix timestamp format. Something like

SELECT extract(epoch from DATE_TRUNC($__timeFrom())

Then in the influx query, you could make 3 separate queries, each with WHERE time = ${mydate} + 10h

I haven’t tested this exact syntax, but it should work, perhaps with some minor modifications.

3

As you require rates from three distinct time you should simply modify you query to extract only those three specific times.

You can use three queries A, B and C The first for 10h00 the second for 15h00 and the third for 18h00.

Then you can use the Transformations to merge them into a single table.

Alternatively you could use your current query and the Computed columns, Filter, Group by Tab to do same