How to parse multiple values from a single string field?

InfluxDB 2
, ,
1

Home Assistant provides day ahead electricity prices as attributes of the entity “current electricity price”, so in InfluxDB those values appear all together in the field “prices_str” as string:

(InfluxQL query for compactness :slight_smile:

SELECT first("prices_str") FROM "€/kWh" WHERE ("entity_id"::tag = 'current_electricity_market_price') AND $timeFilter GROUP BY time(1d) fill(null)

The output is (newlines introduced by me):

Time: 23/03/2026, 01:00:00 

First:
[
{‘from’: datetime.datetime(2026, 3, 22, 23, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 0, 0, tzinfo=tzutc()), ‘price’: 0.12251}, 
{‘from’: datetime.datetime(2026, 3, 23, 0, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 1, 0, tzinfo=tzutc()), ‘price’: 0.12084}, 
{‘from’: datetime.datetime(2026, 3, 23, 1, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 2, 0, tzinfo=tzutc()), ‘price’: 0.12097}, 
{‘from’: datetime.datetime(2026, 3, 23, 2, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 3, 0, tzinfo=tzutc()), ‘price’: 0.11966}, 
{‘from’: datetime.datetime(2026, 3, 23, 3, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 4, 0, tzinfo=tzutc()), ‘price’: 0.12543}, 
{‘from’: datetime.datetime(2026, 3, 23, 4, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 5, 0, tzinfo=tzutc()), ‘price’: 0.14252}, 
{‘from’: datetime.datetime(2026, 3, 23, 5, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 6, 0, tzinfo=tzutc()), ‘price’: 0.17483}, 
{‘from’: datetime.datetime(2026, 3, 23, 6, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 7, 0, tzinfo=tzutc()), ‘price’: 0.18474}, 
{‘from’: datetime.datetime(2026, 3, 23, 7, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 8, 0, tzinfo=tzutc()), ‘price’: 0.14784}, 
{‘from’: datetime.datetime(2026, 3, 23, 8, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 9, 0, tzinfo=tzutc()), ‘price’: 0.10681}, 
{‘from’: datetime.datetime(2026, 3, 23, 9, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 10, 0, tzinfo=tzutc()), ‘price’: 0.07596}, 
{‘from’: datetime.datetime(2026, 3, 23, 10, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 11, 0, tzinfo=tzutc()), ‘price’: 0.0452}, 
{‘from’: datetime.datetime(2026, 3, 23, 11, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 12, 0, tzinfo=tzutc()), ‘price’: 0.04671}, 
{‘from’: datetime.datetime(2026, 3, 23, 12, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 13, 0, tzinfo=tzutc()), ‘price’: 0.06637}, 
{‘from’: datetime.datetime(2026, 3, 23, 13, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 14, 0, tzinfo=tzutc()), ‘price’: 0.08541}, 
{‘from’: datetime.datetime(2026, 3, 23, 14, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 15, 0, tzinfo=tzutc()), ‘price’: 0.102}, 
{‘from’: datetime.datetime(2026, 3, 23, 15, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 16, 0, tzinfo=tzutc()), ‘price’: 0.12346}, 
{‘from’: datetime.datetime(2026, 3, 23, 16, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 17, 0, tzinfo=tzutc()), ‘price’: 0.1729}, 
{‘from’: datetime.datetime(2026, 3, 23, 17, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 18, 0, tzinfo=tzutc()), ‘price’: 0.2495}, 
{‘from’: datetime.datetime(2026, 3, 23, 18, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 19, 0, tzinfo=tzutc()), ‘price’: 0.2389}, 
{‘from’: datetime.datetime(2026, 3, 23, 19, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 20, 0, tzinfo=tzutc()), ‘price’: 0.1995}, 
{‘from’: datetime.datetime(2026, 3, 23, 20, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 21, 0, tzinfo=tzutc()), ‘price’: 0.16809}, 
{‘from’: datetime.datetime(2026, 3, 23, 21, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 22, 0, tzinfo=tzutc()), ‘price’: 0.15219}, 
{‘from’: datetime.datetime(2026, 3, 23, 22, 0, tzinfo=tzutc()), ‘till’: datetime.datetime(2026, 3, 23, 23, 0, tzinfo=tzutc()), ‘price’: 0.13706}
]

As you can see the time fields start at 23:00 of the previous day, since my timezone is +1.

I want to produce an output which can be plotted by Grafana (for example) so that I can have in the morning the hourly energy prices. The query will never be run for days in the past, since it makes sense only as forecast.

Can it be done in Flux?

2

Well I’m amazed.

I post it here for future reference, GPT-5.4 did it.

And it works.

Maybe by chance, but it got the timezone right too (or Grafana did).

import "array"
import "strings"
import "regexp"
import "date"

pad2 = (s) => if strings.strlen(v: s) == 1 then "0" + s else s

fromToRFC3339 = (entry) => {
    fromPart = regexp.findString(r: /'from': datetime\.datetime\([^)]+\)/, v: entry)

    cleaned = strings.replaceAll(
        v: strings.replaceAll(
            v: fromPart,
            t: "'from': datetime.datetime(",
            u: "",
        ),
        t: ", tzinfo=tzutc())",
        u: "",
    )

    parts = strings.split(v: cleaned, t: ", ")

    return
        parts[0] + "-" +
        pad2(s: parts[1]) + "-" +
        pad2(s: parts[2]) + "T" +
        pad2(s: parts[3]) + ":" +
        pad2(s: parts[4]) + ":00Z"
}

entryToPrice = (entry) => {
    pricePart = regexp.findString(r: /'price': [0-9.]+/, v: entry)
    return float(v: strings.replaceAll(v: pricePart, t: "'price': ", u: ""))
}

raw =
    from(bucket: "home_assistant/autogen")
        |> range(start: today(), stop: date.add(d: 1d, to: today()))
        |> filter(fn: (r) =>
            r._measurement == "€/kWh" and
            r.entity_id == "current_electricity_market_price" and
            r._field == "prices_str"
        )
        |> first()

rawValues = raw |> findColumn(fn: (key) => true, column: "_value")
pricesStr = rawValues[0]

body = strings.replaceAll(
    v: strings.replaceAll(v: pricesStr, t: "[", u: ""),
    t: "]",
    u: "",
)

entries = strings.split(v: body, t: "}, {")

rows = array.map(
    arr: entries,
    fn: (x) => ({
        _time: time(v: fromToRFC3339(entry: x)),
        _value: entryToPrice(entry: x),
    }),
)

array.from(rows: rows)
    |> rename(columns: {_value: "price"})
    |> keep(columns: ["_time", "price"])

Again, I’m amazed.

By Flux and by GPT-5.4.

Also, GPT-5.4 is NOT able to produce a SQL query for InfluxDB v3 to perform the same.

3

nice to hear that, thought Claude was best but GPT 5.4 seems pretty good for Flux.

4

Last year I was using Gemini 2.5 Pro because GPT-5 was not satisfactory, but now I didn’t need to go to Gemini, I used the normal GPT-5.4 Thinking (not auto or instant) available with my Plus subscription.

This was normal GPT chat, not Codex, which I also used very successfully to add feature and fix bugs in a Home Assistant integration, but which requires coupling to a Github repository so not suitable for this task.