Can you help me build a proper SELECT statement to do what I want?

InfluxDB 2 Dashboards
,
1

Hi,

I have ran into a problem with InfluxDB. I’m using Grafana for visualization of my data. First, here is some brief context:
My database contains power consumption figures and (solar) power production figures for my entire house.

Here is what I want, in English:

Show me how much power I consumed for the last week, per day, from 00:00 to 00:00 (local time) each day. In other words, I want to query a 24 hour time span beginning at midnight of one day and ending at midnight the next, for each day of the previous 7 days.

Here is what I have:

Multiple queries, one for each day:

7 days ago:

SELECT integral("power") /3600000 FROM "solar" WHERE ("time" > now() - 7d AND "time" < now()-6d)

6 days ago:

SELECT integral("power") /3600000 FROM "solar" WHERE ("time" > now()-6d AND "time" < now()-5d)

… And so on, for each day. The individual points in my DB are a measure of Watts, so the integral over 3600000 gives me the kilowatt hours.

Here is the issue:

The now() function, as I’m sure you’re aware, returns the current time. So, if I execute the queries now, at 4:15pm, the results are actually showing me from 4:15pm yesterday until now… from 4:15pm 2 days ago until yesterday at 4:15pm… etc.

I don’t know how - or if it’s even possible in InfluxDB - to build a query to get the time, relative to now(), that begins at 00:00 and ends at 00:00 the next day.

I have seen the stale issue here which describes the same functionality I need, but no solutions have been posted.

If it’s not possible to do in InfluxDB, I will explore the possibility of having Grafana build the dynamic query. If anyone knows how to do it in Grafana, it would be extremely helpful for you to share it here, though I realize this is not a Grafana forum…

Thanks for the help!

2

I don’t have a direct solution to offer, I’m also newish to influxdb.

One problem I see with your approach is that if you ever need to reprocess your data after the fact it becomes much more challenging, since the timestamp applied to the group is ‘now’ instead of relative to the data being queried.

There might be some valid reason to continue with your approach, but it might be worth consisting using the query range start or end time for the query - although that concept may only exist in the new flux language .

In sql style im thinking of a sub query that selects top1 or max, and use that within a group by.

3

Thanks for the input! I’m not sure that I follow the logic in your response. If I ever wanted to reprocess the data, I can always create a new query to do what I want - OR, I can write something in Python to process the raw data from InfluxDB. Am I misunderstanding your point?

Maybe it would help if I explained the purpose of the query with some additional context. I built a dashboard for my home energy situation, including total consumption and total production. using Grafana. The data source for the dashboard is InfluxDB. When I load my Grafana dashboard in my browser, it executes specific queries and visualizes them how I’ve configured it to.

I want the dashboard to show me, for each day of the previous 7 days, what my total power consumption is, per day, beginning at midnight, and ending at midnight the next day. Hard coding a specific date in the query defeats the purpose of the dashboard, which is why I’m trying to use a relative query. If I pull up my dashboard next week, I want it to show me the data for the previous week relative to the time I view the dashboard. The problem is, if I load the dashboard at 7:03AM, the queries are executed not from midnight of each day, but from 7:03AM each day, because the query is built relative to now(), not "now() at midnight".

Here’s an example of the issue, visualized.

image
image2560×1298 157 KB

4

what does a few rows of the raw data look like?

I see what you mean with that ticket - need a function that does something like now(YYYY-MM-DD 00:00:00) where the time componant is locked to midnight.

The bottom guy on that ticket you linked to worked around it by constructing a query externally and connnecting via the api to run it when needed…

5

Looks like you could do something in flux language, but I think you need to enable it under 1.x

Doco here:

https://docs.influxdata.com/flux/v0.65/stdlib/date/truncate/#sidebar

6

Hey, sorry about the slow reply. I got slammed over the weekend.

Here are a few rows of the raw data:

select * from solar limit 5
name: solar
time                current           pf                 power
----                -------           --                 -----
1586211317025000000 35.95738042322411 0.997309079650841  4485.692015725273
1586211318917000000 35.96088509039768 0.9975542193560375 4491.308277554761
1586211320607000000 35.8308953632707  0.9971828644695451 4464.181688722756
1586211322303000000 36.11533062146914 0.9975788506245213 4505.165453870696
1586211324043000000 36.3329165249273  0.997281692206817  4529.150260250118

I will look into the truncate function. I agree that this is probably something that will need to be created externally. I’m already using Grafana as a source of external queries, so I will have to investigate if Grafana can do it.

Thank you for the help! If I find a way to have Grafana do it, I will post here.