Postgre - Barchart - Show difference between values

martinbengtzon · April 28, 2024, 6:12pm

Hello,

I am moving over from Influx to Postgres, had a great run, and now the shitty stuff comes

What was easy in Influx, was for example, when i queries a table with energy consumption. It is a value that appends all the time so in Influx i did this:

from(bucket: "Metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Electrical")
  |> filter(fn: (r) => r["_field"] == "act_energy_pos")
  |> aggregateWindow(every: 1h, fn: first, createEmpty: false)
  |> difference()

This will show this:

Works like a charm. Takes values for 30 minutes or 1 hour, and show the difference between them.

Now doing this in Postgres seems very very advanced, thus i do not know if am moving into the wrong direction: Here is the Setup in Postgres

Table/columns

No matter how i try, there is no aggregate function for difference, but only Sum/Avg etc.

Hopefully someone can show me an easy example how to do this, maybe Influx is a lot better for this kind of stuff, if you dont want to over complicate queries.

Best regards,

Martin

yosiasz · April 28, 2024, 8:06pm

good move as flux is on the way out

can you click query on both and show what the data looks for both

martinbengtzon · April 28, 2024, 8:25pm

Yea, i understood that too Problem as i see it, the Postgres does not have a built in function / aggregate function for a difference() as Flux has. Also, flux has better cooperation with the graphs.

Thanks for the reply!

Here is the Postgres:

SELECT
  time_bucket('3600.000s',"time") AS "time",
  avg(act_energy_pos) AS "act_energy_pos"
FROM powermetrics
WHERE
  "time" BETWEEN '2024-04-28T12:11:12.869Z' AND '2024-04-28T18:11:12.869Z'
GROUP BY 1
ORDER BY 1

###
Result

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "uid": "UyNygaBIz",
            "type": "postgres"
          },
          "rawSql": "SELECT\n  $__timeGroupAlias(\"time\",1h),\n  avg(act_energy_pos) AS \"act_energy_pos\"\nFROM powermetrics\nWHERE\n  $__timeFilter(\"time\")\nGROUP BY 1\nORDER BY 1",
          "format": "time_series",
          "datasourceId": 6,
          "intervalMs": 60000,
          "maxDataPoints": 1080
        }
      ],
      "range": {
        "from": "2024-04-28T14:24:41.873Z",
        "to": "2024-04-28T20:24:41.873Z",
        "raw": {
          "from": "now-6h",
          "to": "now"
        }
      },
      "from": "1714314281873",
      "to": "1714335881873"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "frames": [
          {
            "schema": {
              "refId": "A",
              "meta": {
                "executedQueryString": "SELECT\n  time_bucket('3600.000s',\"time\") AS \"time\",\n  avg(act_energy_pos) AS \"act_energy_pos\"\nFROM powermetrics\nWHERE\n  \"time\" BETWEEN '2024-04-28T14:24:41.873Z' AND '2024-04-28T20:24:41.873Z'\nGROUP BY 1\nORDER BY 1"
              },
              "fields": [
                {
                  "name": "Time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "act_energy_pos",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1714312800000,
                  1714316400000,
                  1714320000000,
                  1714323600000,
                  1714327200000,
                  1714330800000,
                  1714334400000
                ],
                [
                  101823028.61320755,
                  101825447.29526462,
                  101831078.53333333,
                  101835502.73055556,
                  101840836.55555557,
                  101844852.23333333,
                  101847557.02702703
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

Here is the Flux:

from(bucket: "Metrics")
  |> range(start: 2024-04-28T14:22:57.316Z, stop: 2024-04-28T20:22:57.316Z)
  |> filter(fn: (r) => r["_measurement"] == "Electrical")
  |> filter(fn: (r) => r["_field"] == "act_energy_pos")
  |> aggregateWindow(every: 1h, fn: first, createEmpty: false)
  |> difference()

###
Result:

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "datasource": {
            "uid": "2AcxRoKVz",
            "type": "influxdb"
          },
          "query": "from(bucket: \"Metrics\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Electrical\")\n  |> filter(fn: (r) => r[\"_field\"] == \"act_energy_pos\")\n  |> aggregateWindow(every: 1h, fn: first, createEmpty: false)\n  |> difference()",
          "refId": "A",
          "datasourceId": 1,
          "intervalMs": 20000,
          "maxDataPoints": 1080
        }
      ],
      "range": {
        "from": "2024-04-28T14:23:34.925Z",
        "to": "2024-04-28T20:23:34.925Z",
        "raw": {
          "from": "now-6h",
          "to": "now"
        }
      },
      "from": "1714314214925",
      "to": "1714335814925"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "frames": [
          {
            "schema": {
              "name": "Electrical",
              "refId": "A",
              "meta": {
                "executedQueryString": "from(bucket: \"Metrics\")\n  |> range(start: 2024-04-28T14:23:34.925Z, stop: 2024-04-28T20:23:34.925Z)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Electrical\")\n  |> filter(fn: (r) => r[\"_field\"] == \"act_energy_pos\")\n  |> aggregateWindow(every: 1h, fn: first, createEmpty: false)\n  |> difference()"
              },
              "fields": [
                {
                  "name": "Time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "act_energy_pos",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  },
                  "labels": {}
                }
              ]
            },
            "data": {
              "values": [
                [
                  1714320000000,
                  1714323600000,
                  1714327200000,
                  1714330800000,
                  1714334400000,
                  1714335814925
                ],
                [
                  2167,
                  3472,
                  6036,
                  4345,
                  5182,
                  3564
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

yosiasz · April 28, 2024, 8:44pm

oops I meant to say the Data View screen shot

martinbengtzon · April 29, 2024, 4:09am

Second