Time format in dbClient.query(...)

,
1

Hello to all

I have following problem.
I use following query, which is working properly in my python script …

results_min = dbClient.query(‘select min(ENERGY_Today) from mqtt_consumer’WHERE time > '2021-01-01' and time < '2021-02-06'’)

Now i would like to replace the “time strings” ('2021-01-01' …) with a variable containing the desired timestamp …

I get error when trying … what is false with that idea ?
Do you know a solution to that?

Many thanks

2

Firstly, please tell us what the error says.

Secondly , please show us how you tried to specify the variable.

Basically, if we don’t know what you did, and you don’t tell us what the
system complained at, we have no idea what to suggest you do instead.

People here are happy to help, but need enough informaton to work with.

Antony.

3

Sorry
Here is more of my code

from influxdb import InfluxDBClient
import time
from datetime import datetime

now = datetime.now().time() # time object
current_time = now.strftime(“%H-%M-%S”)

dbClient = InfluxDBClient(database=‘PV_data’)

results_max = dbClient.query(‘select max(ENERGY_Today) from mqtt_consumer WHERE time < current_time’)
results_min = dbClient.query(‘select min(ENERGY_Today) from mqtt_consumer WHERE time > '2021-01-01' and time < '2021-02-06'’)

for measurement in results_max.get_points(measurement=‘mqtt_consumer’):
kW_yesterday_max = measurement[‘max’]
zeit_max = measurement[‘time’]

for measurement in results_min.get_points(measurement=‘mqtt_consumer’):
kW_yesterday_min = measurement[‘min’]
zeit_min=measurement[‘time’]

print(“Maximum\n”,kW_yesterday_max,“\n”, zeit_max,“\n”,“\nMinimum\n”, kW_yesterday_min,“\n”, zeit_min)
print(“\nDifferenz:\n”, (kW_yesterday_max-kW_yesterday_min))

4

… and here the error log …

Python 3.7.3 (/usr/bin/python3)

%Run db_write_basic_kombiniert.py
Traceback (most recent call last):
File “/home/pi/Python_Scripts/db_write_basic_kombiniert.py”, line 11, in
results_max = dbClient.query(‘select max(ENERGY_Today) from mqtt_consumer WHERE time < current_time’)
File “/usr/local/lib/python3.7/dist-packages/influxdb/client.py”, line 539, in query
in data.get(‘results’, )
File “/usr/local/lib/python3.7/dist-packages/influxdb/client.py”, line 538, in
for result
File “/usr/local/lib/python3.7/dist-packages/influxdb/resultset.py”, line 25, in init
raise InfluxDBClientError(self.error)
influxdb.exceptions.InfluxDBClientError: invalid operation: time and *influxql.VarRef are not compatible

5

My first guess would be to put escaped single quotes around current_time in the
query, same as you have them aroudn the hard-coded strings.

However, seeing what the error message is might be even more helpful to
getting a definite answer from someone.

Antony.

6

In addition to my suggestion re escaped single quote marks, I have two
comments:

results_max = dbClient.query(‘select max(ENERGY_Today) from mqtt_consumer
WHERE time < current_time’)

results_min = dbClient.query(‘select min(ENERGY_Today) from mqtt_consumer
WHERE time > '2021-01-01' and time < '2021-02-06'’)

  1. In one query you are comparing the “time” field in your database with a
    timestamp; in the other you are comparing with a datestamp. This seems odd to
    me.

  2. Does your mqtt_consumer table only contain data from today? If not, and
    it includes data from previous days, then a query with “where time <
    current_time” is going to show you the maxmimum value from any day in the
    past, not just today.

Antony.

7

Hi Antony

Many thanks for your comment and help.

@1: it ist just for checking, i tried also with current_date, without success (i am afraid it is working with strings only, not with avariable of type string …)
@2:that is correct, you know … this code is just for verifying if the coding works, not already taking care of the logic …
Toni

8

Hi

In the meantime i realised that this is working (now() + or - some days)

results_max = dbClient.query(‘select max(ENERGY_Today) from mqtt_consumer WHERE time < now() -1d’)

But still no success with other “timestamp” references as asked for above …