Filter by time but not date or drop first record...?

[dyslexicbloke]

Hi folks,
I am very new to all this, I had some SQL experience years ago so the concepts feel familiar but assuming I know nothing, or worse have made some faulty assumptions, is where my head is right now so please bear that in mind when I ask inevitably stupid stuff !

Specifics… Well as close as I can get right now.
I have an Influx database, and Grafana, running on a Home Assistant RPI
One of my sensors, so data, is kWh, and is attached to multiple entities.

I am not going to pretend that I know the table/s structure but obviously it must contain a timestamp and the data at least.

I built this, using the GUI :-

SELECT max(“value”) FROM “kWh” WHERE (“entity_id”::tag = ‘caravan_and_workshop_4_1d’) AND $timeFilter ORDER BY time DESC

and then modified it to :-

SELECT “value” AS “Caravan” FROM “kWh” WHERE (“entity_id”::tag = ‘caravan_and_workshop_4_1d’) AND $timeFilter GROUP BY interval_day
with an aggregate MAX in the value options of the card.

The latter, sort of works but there is a data anomaly causing a problem.
The data is collected every minute, and being cumulative, is an ascending value.
For any $TimeFilter period, or any other method of getting a dataset, I need to group by a date, not time, and return a Max value.
The issue is that the first record in most ‘days’ is actually the last value of the previous day, typically captured 15-20 seconds after midnight so at 00:10… I have 10 records but only 9 of them are actually the correct day, from a value perspective, with all having a timestamp, and associated date for the group, that is in range.

Timeshift seemed an obvious solution but I cant get it to work, mor likely do not know what I am doing, so I started looking at how to do something like this:-

SELECT “value” AS “Caravan” FROM “kWh”
WHERE (“entity_id”::tag = ‘caravan_and_workshop_4_1d’) AND $timeFilter AND time > 00:01:00.000
GROUP BY interval_day

AND time > 00:01:00.000 just gives me an error and I have tried multiple ways of specifying the time to do this but none so far have helped.
A couple of attempts, looked promising, not that I can remember which, but anything that wasn’t simply wrong syntax complained about not being able to compare time to a literal.

Obviously I am missing something and data anomalies like this are common so this must be possible and should be easy…

Please help if you can… I am feeling more than a little silly here, filter by time or drop the first record of a row set is hardly rocket science.

Cheers,
Al

[ebabeshko]

May be aggregation using LAST() would help you.

See this topic:

How to query the maximum value per day, but only where the hour is 23

I have InfluxDB being populated by Home Assistant and am trying to produce a ‘maximum’ value per day chart. The metric being graphed is the ongoing savings made in fuel, so is constantly rising and reverts to zero at midnght. What I have is working except the data isn’t brilliant as there are often values just after midnight that are clearly from the previous day, before whatever process drops the value back down to zero. My query is currently: SELECT max("value") FROM "GBP" WHERE ("entity_id…

[dyslexicbloke]

Thanks that is helpful BUT my lack of understanding still leaves me with questions.

I had tried Last, well Last * at least, and it sort of works.
I have, at least I think I have, a couple of concerns…

1. I do not know what the native order is so do not know if first/last would be consistent.

2. If $timeFilter spans multiple days I would ideally like multiple records, of max(value) grouped by day. Once I add Max in the query I am including the bad value… Hang on… You made me re-evaluate…

That gave me an idea whilst typing and it appears I have bigger problems in that
GROUP BY interval_day
isn’t working.

Tried datepart() but aparantly that isn’t right either.

The strategy you suggest will likely work if I can get the GROUP right…
GROUP BY time(1d) looks promising, playing…

[dyslexicbloke]

OK…
so this:-
SELECT max(“value”) FROM “kWh” WHERE (“entity_id”::tag = ‘caravan_and_workshop_4_1d’) AND $timeFilter GROUP BY time(1d) ORDER BY time(1d) DESC
seems to work with QI returning the records I expect,all be it still with one extra day:- [From QI]

SELECT max(“value”) FROM “kWh” WHERE (“entity_id”::tag = ‘caravan_and_workshop_4_1d’) AND time >= 1717023600000ms and time <= 1717455599000ms GROUP BY time(1d) ORDER BY time DESC

2024-06-03 01:00:00 989 Wh
2024-06-02 01:00:00 1.59 kWh
2024-06-01 01:00:00 1.16 kWh
2024-05-31 01:00:00

Playing with timeshift and relative isn’t helping, I need to drop the single record from 31st. Using Last, or first for that matter, as suggested earlier gets me either the first or last day, which is better than I was getting before, but isn’t going to help with data that needs to be represented for multiple days.

In an Ideal world the data, which is feeding a simple vertical, bar right now would be filling a histogram, or perhaps SD, that gives an impression of the value spread over the period whilst still displaying the max and min values.
I guess t bars, Min/Max/Avg would do but even at that the stray day will mess things up.

(Just to clarify I mean Min / Avg / Max of the grouped days not an individual day)

Can I modify/transform the contents of $timeFilter? just adding1 minute to the first term would do, yes I know I would still not be seeing the last record for the latest day and running the query at 00:00:nn would miss a day but I can live with that, If I have to, the data is what it is.