SQL-problem

[BartSr]

I use this query in a dzVents script of late Waaren ( viewtopic.php?t=30725 )

collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Usage Usage, b.Date Date '..

' FROM DeviceStatus a , Meter b WHERE AND a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ')'
of course this is OK.

Now I modified to limit to output using:

collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Usage Usage, b.Date Date '..

' FROM DeviceStatus a , Meter b WHERE time(b.Date) = "07:00:01" AND a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ')'

note extended where-clause : time(b.Date) = "07:00:01" AND

But ....
When I create the query in DB Browser for SQLite it's OK. Output as expected.
Same query in Domoticz scripts: no output. Logfile shows ERROR ==>> but no errormessage.
Anyone who knows how to solve?

Thanks.
-Bart

[willemd]

"time" is probably not a function known within dzVents. You will have to use a different method to get the time part of the date field that you selected, depending on what type the date field is. Probably a string?

[BartSr]

Date is datetime type. So how to get the timeselectio?

[willemd]

Can you find it here?
https://www.domoticz.com/wiki/DzVents:_ ... nd_methods

[waltervl]

the time statement is in the SQL query so no DzVents option.
I think it has to do with formatting the string and DzVents gets confused? Try to play with quote ' and double quotes "

Code: Select all

collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Usage Usage, b.Date Date FROM DeviceStatus a , Meter b WHERE time(b.Date) = "07:00:01" AND a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ')'

[BartSr]

Hi Waltervl,
Quotes, double quotes and square quotes. All the same.
Bart

[willemd]

and what about a backslash before the double quotes before and after the time ( so \"07:00:01\" ), to indicate to dzVents these should not be treated as double quotes but just past as is.

[waltervl]

Also there is a colon ":" in the string (due to the time 7:00:00 ) that perhaps mixes things up in lua table collects{}???