Solar system / Best day of the month

Hello my name is Peter and I need your support.
Unfortunately, my english is not that good.
To the background. I have written a small program under PHP that omits my solar system.
As a database I have influx 1.7 and grafana for visualization.

I want to query from my database the best day of the month.

I have already tried many things but do not continue. Now I hope for you :slight_smile:

The data is written to the table P-Solar every minute.

SELECT “P-Solar” FROM “PV” WHERE …

Hello my name is Peter and I need your support.
Unfortunately, my english is not that good.
To the background. I have written a small program under PHP that omits my
solar system.

Please can you clarify the word “omits”, as this does not make sense in that
context.

As a database I have influx 1.7 and grafana for visualization.

I want to query from my database the best day of the month.

How do you define “best”?

I have already tried many things but do not continue. Now I hope for you :slight_smile:

The data is written to the table P-Solar every minute.

SELECT “P-Solar” FROM “PV” WHERE …

We need to know how you as a human would calculate the “best day of the month”
in order to help you tell a computer to do it for you :slight_smile:

Antony.

Thx Antonius :slight_smile:

Every minute, the power produced by the sun is written to the database.

The best day of the month would be the day that has the highest value as a sum

I hope that is understandable.

Hello Peter,

This will show you the sum each day …
SELECT SUM(“P-Solar”) FROM “PV” WHERE time>‘2019-10-01’ GROUP BY TIME(1d)

You might need to change the WHERE clause to select the right measurements.
You might want to change the GROUP BY TIME to specify a timezone offset if you are not UTC, e.g.
GROUP BY TIME(1d,-3600s)

I don’t think you can have Influx automatically pick out the day with the highest sum, but at least you can get the sum for each day very easily.

If you are recording the kWH each day, and the inverter resets kWH to 0 each morning, and that value is stored as “E-Solar”, you can do this:
SELECT max(“E-Solar”) FROM “PV” WHERE time>‘2019-01-01’ GROUP BY TIME(1d)

Jeremy

Hello, thank you all!

But that’s my requirement.

Which day of the month has the highest value as the sum.

Here’s the table
TIME P-SOLAR
09.10.2019 13:18 605.11
09.10.2019 13:17 616.99
09.10.2019 13:16 659.33

is there a trick?

with two queries?

You can get the timestamp and the highest value by nesting the queries. Using @JeremySTX 's query as a base

SELECT max(“DAY_TOTAL”) FROM (SELECT SUM(“P-Solar”) AS “DAY_TOTAL” FROM “PV” WHERE time>‘2019-10-01’ GROUP BY TIME(1d))

If you want to return the day as the column of interest you probably need to use flux