data.service.js vs postgres sql query error #16

matthiasgasser · 2019-01-28T22:39:41Z

Vanilla habpanel install on iobroker.
postgres as history adapter

tried to configure the charting widget with some time series data (tried temperature and power consumption)

fails because of malformed query:
SQL log:

error: syntax error at or near "T22"

SQL statement generated:
STATEMENT: SELECT ts, val FROM ts_number WHERE ts_number.id=1753 AND ts_number.ts < 1548719200089 AND ts_number.ts >= 2019-01-27T22:23:18.947Z UNION ( SELECT ts, val FROM ts_number WHERE ts_number.id=1753 AND ts_number.ts < 2019-01-27T22:23:18.947Z ORDER BY ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM ts_number WHERE ts_number.id=1753 AND ts_number.ts >= 1548719200089 ORDER BY ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;

obviously postgres is expecting a timestamp (i.e. 1548719200089) and the query generated generates a datestring (i.e. 2019-01-27T22:23:18.947Z)

I am not familiar "who's" in charge for generating the correct query? Is this the sql adapters fault? or should the data.service.js call the history service with a timestamp rather a datetime string?

Thanks for looking into this!

Apollon77 · 2019-01-28T23:49:58Z

Please open issue in sql adapter. Should be there.

Apollon77 · 2019-01-29T08:04:26Z

No, it should be somewhere else ... it seems that "options.start" is correctly a number (ms), but options.end is a "Date". Currently the history adapters are not checking this and so this problem ocurs.

matthiasgasser · 2019-01-29T13:14:06Z

Thanks for your swift answer. I'll look into the sql adapter!

Apollon77 · 2019-01-29T19:01:14Z

Stop. Please see my last comment. It comes from this adapter.

matthiasgasser · 2019-01-29T19:04:05Z

Understood, but I thought while it’s true that different data types are thrown at the sql adapter, the common opinion is that the sql adapter needs to be able to handle different date objects/formats.

Apollon77 · 2019-01-29T21:42:50Z

Thw definition of the API "getHistory" is that Timestamps are accepted :-) Yes Error handling is missing there ... that would be worth a sql issue :-)

foxthefox · 2019-01-30T21:35:54Z

should be solved with PR#19

Apollon77 · 2019-01-30T22:05:34Z

v0.3.2 of habpanel is on npm

holger05 · 2019-01-31T13:21:40Z

Still the same with 0.3.2:
2019-01-31 14:17:50.035 - error: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':17:49.507Z UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_' at line 1

Apollon77 · 2019-01-31T15:32:18Z

Ok, please set sql to Debug log mode and show the graph again and send the log from sql please

holger05 · 2019-01-31T15:37:12Z

Here you are:
`

2019-01-31 16:36:00.207 - info: phantomjs.0 Create 1200px*800px in 10000ms - "http://192.168.178.63:8082/vis/index.html#Wetter_Ext" => "/opt/iobroker/node_modules/iobroker.phantomjs/wetter-ext.png"

2019-01-31 16:36:00.197 - info: javascript.0 script.js.common.Wetter_Ext: exec: wput -nc /opt/iobroker/node_modules/iobroker.phantomjs/wetter-ext.png ftp://u82798514-iobroker:iobroker@home598621085.1and1-data.host
2019-01-31 16:36:00.254 - info: javascript.0 script.js.common.Wetter_Ext: exec: wput -nc /opt/iobroker/node_modules/iobroker.phantomjs/wetter-ext.png ftp://u82798514-iobroker:iobroker@home598621085.1and1-data.host
2019-01-31 16:36:02.529 - info: web.0 ==>Connected system.user.admin from ::ffff:192.168.178.63
2019-01-31 16:36:04.896 - info: web.0 ==>Connected system.user.admin from ::ffff:192.168.178.63
2019-01-31 16:36:05.167 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.168 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.234 - debug: sql.0 Send: 1330 of: 2421 in: 67ms
2019-01-31 16:36:05.235 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:05.321 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.323 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.345 - debug: sql.0 Send: 192 of: 218 in: 24ms
2019-01-31 16:36:05.345 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:05.365 - info: web.0 <==Disconnect system.user.admin from ::ffff:192.168.178.110 habpanel.0
2019-01-31 16:36:05.424 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.425 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548948964889 AND iobroker.ts_number.ts >= 1548776164889 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548776164889 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts >= 1548948964889 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:05.472 - debug: sql.0 Send: 986 of: 1226 in: 48ms
2019-01-31 16:36:05.473 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:06.592 - info: web.0 ==>Connected system.user.admin from ::ffff:192.168.178.110
2019-01-31 16:36:07.975 - debug: sql.0 SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts < 1548948967306 AND iobroker.ts_bool.ts >= 2019-01-30T15:36:07.306Z UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts < 2019-01-30T15:36:07.306Z ORDER BY iobroker.ts_bool.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts >= 1548948967306 ORDER BY iobroker.ts_bool.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:07.978 - debug: sql.0 SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts < 1548948967306 AND iobroker.ts_bool.ts >= 2019-01-30T15:36:07.306Z UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts < 2019-01-30T15:36:07.306Z ORDER BY iobroker.ts_bool.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_bool.id=10 AND iobroker.ts_bool.ts >= 1548948967306 ORDER BY iobroker.ts_bool.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:08.003 - error: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':36:07.306Z UNION ( SELECT ts, val FROM iobroker.ts_bool WHERE iobroker.ts_' at line 1
2019-01-31 16:36:08.004 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:09.446 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts < 1548948969437 AND iobroker.ts_number.ts >= 2019-01-30T15:36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts < 2019-01-30T15:36:07.286Z ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts >= 1548948969437 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:09.447 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts < 1548948969437 AND iobroker.ts_number.ts >= 2019-01-30T15:36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts < 2019-01-30T15:36:07.286Z ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=101 AND iobroker.ts_number.ts >= 1548948969437 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:09.463 - error: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.t' at line 1
2019-01-31 16:36:09.463 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:09.515 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts < 1548948969437 AND iobroker.ts_number.ts >= 2019-01-30T15:36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts < 2019-01-30T15:36:07.286Z ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts >= 1548948969437 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:09.516 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts < 1548948969437 AND iobroker.ts_number.ts >= 2019-01-30T15:36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts < 2019-01-30T15:36:07.286Z ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=1 AND iobroker.ts_number.ts >= 1548948969437 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:09.535 - error: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':36:07.286Z UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.t' at line 1
2019-01-31 16:36:09.536 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:12.734 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:12.735 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=31 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:12.832 - debug: sql.0 Send: 1309 of: 2420 in: 98ms
2019-01-31 16:36:12.838 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:13.055 - info: web.0 <==Disconnect system.user.admin from ::ffff:192.168.178.63 vis.0
2019-01-31 16:36:13.067 - info: web.0 <==Disconnect system.user.admin from ::ffff:192.168.178.63 flot
2019-01-31 16:36:13.737 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.739 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=30 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.748 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.751 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=32 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.763 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.763 - debug: sql.0 SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts < 1548948972725 AND iobroker.ts_number.ts >= 1548776172725 UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts < 1548776172725 ORDER BY iobroker.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker.ts_number WHERE iobroker.ts_number.id=98 AND iobroker.ts_number.ts >= 1548948972725 ORDER BY iobroker.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;
2019-01-31 16:36:13.793 - debug: sql.0 Send: 192 of: 218 in: 56ms
2019-01-31 16:36:13.794 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:13.801 - debug: sql.0 Send: 296 of: 313 in: 38ms
2019-01-31 16:36:13.802 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:13.828 - debug: sql.0 Send: 986 of: 1226 in: 79ms
2019-01-31 16:36:13.828 - debug: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0
2019-01-31 16:36:26.897 - debug: sql.0 new value received for ble.0.c4:7c:8d:66:43:31.fertility, new-value=157, ts=1548948986891, relog=false
2019-01-31 16:36:26.898 - debug: sql.0 Min-Delta reached ble.0.c4:7c:8d:66:43:31.fertility, last-value=156, new-value=157, ts=1548948986891
2019-01-31 16:36:31.833 - debug: sql.0 new value received for hm-rpc.0.LEQ1344755.2.ENERGY_COUNTER, new-value=161312.099854, ts=1548948991829, relog=false
2019-01-31 16:36:31.833 - debug: sql.0 value not changed hm-rpc.0.LEQ1344755.2.ENERGY_COUNTER, last-value=161312.099854, new-value=161312.099854, ts=1548948991829
2019-01-31 16:36:31.844 - debug: sql.0 new value received for hm-rpc.0.LEQ1344755.2.POWER, new-value=0, ts=1548948991840, relog=false
2019-01-31 16:36:31.844 - debug: sql.0 value not changed hm-rpc.0.LEQ1344755.2.POWER, last-value=0, new-value=0, ts=1548948991840

`

Apollon77 · 2019-01-31T15:46:35Z

There you can see interestingly some requests with correct "timestamp numbers" (maybe from other sources/stats?) and some with a incorrect data string ... @foxthefox

foxthefox · 2019-01-31T20:29:04Z

@holger05
Can you please find out what objects are stored in sql with id=1 and id=101 and which widget is using this?
Right now I do not find anything which could cause the error in charts and timeline.
Are you using imported widgets?

holger05 · 2019-02-01T08:09:27Z

@foxthefox
Where will I get this information from? I have two DP added to the chart. The temperatur from a homematic "Wetterstation" and the temperature from a mihome weather sensor. I tested with only one of this with the same error.

Apollon77 · 2019-02-01T08:30:31Z

You could close habpanel and other visus dat use sql to get data, then set debug log and open the object history settings in admin, go to the "table" tab and then you see in the sql Ligs the queries ... and the ID in there :-)

holger05 · 2019-02-01T08:54:35Z

Where is this ID? Sorry, it is the first time for me.

Apollon77 · 2019-02-01T09:58:02Z

Im Log siehst Du dann die Queries und da ist dann die ID drin. Du kannst zeitlich dann sehen was du im Admin siehst und welche ID in der Query steht

holger05 · 2019-02-01T10:07:27Z

Ich habe jetzt alle Widgets aus dem Panel gelöscht. Nur noch das Chart Widget. Fehler bleibt. Und die ID ist immer vom DP den ich im Chart anwähle.

robig · 2019-02-01T11:39:40Z

Hallo.
Hier ist mein Log:

2019-02-01 12:38:02.731 - �[31merror�[39m: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':38:00.184Z UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobrok' at line 1
`

Kann ich irgendwo den ganzen Query anzeigen? im SQL-Server selbst?

Apollon77 · 2019-02-01T13:23:12Z

Debug log in sql nutzen. Dann steht die komplette Query direkt davor im Log

robig · 2019-02-01T14:07:53Z

Meinst du die Config der Instanz sql.0 ?

Apollon77 · 2019-02-01T14:15:14Z

Instanz sql.0 Loglevel auf debug

robig · 2019-02-01T15:32:35Z

Keine Ahnung wo das geht. in den Adapter - Einstellungen kann ich nichts dergleichen finden.

holger05 · 2019-02-01T15:36:29Z

Keine Ahnung wo das geht. in den Adapter - Einstellungen kann ich nichts dergleichen finden.

In den Instanzen.
Erst den Admin einschalten (das rote Icon in der Mitte):

Und dann den in der Adapter Instanz den Log Level auf "debug":

robig · 2019-02-01T17:43:14Z

ok ... so klappts:
2019-02-01 18:40:12.400 - �[34mdebug�[39m: sql.0 SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts < 1549042812360 AND iobroker_hist.ts_number.ts >= 2019-02-01T05:40:11.687Z UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts < 2019-02-01T05:40:11.687Z ORDER BY iobroker_hist.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts >= 1549042812360 ORDER BY iobroker_hist.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC; 2019-02-01 18:40:12.402 - �[34mdebug�[39m: sql.0 SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts < 1549042812360 AND iobroker_hist.ts_number.ts >= 2019-02-01T05:40:11.687Z UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts < 2019-02-01T05:40:11.687Z ORDER BY iobroker_hist.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobroker_hist.ts_number.id=16 AND iobroker_hist.ts_number.ts >= 1549042812360 ORDER BY iobroker_hist.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC; 2019-02-01 18:40:12.422 - �[31merror�[39m: sql.0 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':40:11.687Z UNION ( SELECT ts, val FROM iobroker_hist.ts_number WHERE iobrok' at line 1
2019-02-01 18:40:12.424 - �[34mdebug�[39m: sql.0 sendTo "getHistory" to system.adapter.web.0 from system.adapter.sql.0`

Ich denke, das Datum muss in Hochkommas oder als timestamp angegeben werden.

Danke!

Apollon77 · 2019-02-01T21:41:39Z

muss timestamp sein

Apollon77 · 2019-02-01T21:47:26Z

Also nach https://github.com/ioBroker/ioBroker.sql/blob/master/lib/postgresql.js#L90 sollte es "options.start" sein. options.end passt (oder ist nicht gesetzt und daher automatisch gesetzt)

robig · 2019-02-04T07:46:51Z

Hallo. Es gibt noch keine Lösung, oder?
btw. ich verwende mysql (MariaDB) statt postgress.

Apollon77 · 2019-02-04T08:45:00Z

idealerweise später heute fixed by #21

Apollon77 · 2019-02-04T21:11:43Z

0.3.4 on npm

robig · 2019-02-05T08:52:12Z

Danke! Funktioniert!
Bitte Issue schließen und kann man dir irgendwie einen Kaffee (oder so) spendieren? ;)

Apollon77 · 2019-02-05T08:57:23Z

Gefixt habens andere aber Danke. Wenn Du was spenden willst dann am besten ans ioBroker Projekt :-)

matthiasgasser closed this as completed Jan 29, 2019

Apollon77 reopened this Jan 29, 2019

Apollon77 closed this as completed Jan 30, 2019

Apollon77 reopened this Jan 31, 2019

Apollon77 closed this as completed Feb 5, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

data.service.js vs postgres sql query error #16

data.service.js vs postgres sql query error #16

matthiasgasser commented Jan 28, 2019

Apollon77 commented Jan 28, 2019

Apollon77 commented Jan 29, 2019

matthiasgasser commented Jan 29, 2019

Apollon77 commented Jan 29, 2019

matthiasgasser commented Jan 29, 2019

Apollon77 commented Jan 29, 2019

foxthefox commented Jan 30, 2019

Apollon77 commented Jan 30, 2019

holger05 commented Jan 31, 2019

Apollon77 commented Jan 31, 2019

holger05 commented Jan 31, 2019 •

edited

Apollon77 commented Jan 31, 2019

foxthefox commented Jan 31, 2019 •

edited

holger05 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

holger05 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

holger05 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 1, 2019

holger05 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 4, 2019

Apollon77 commented Feb 4, 2019

Apollon77 commented Feb 4, 2019

robig commented Feb 5, 2019

Apollon77 commented Feb 5, 2019

data.service.js vs postgres sql query error #16

data.service.js vs postgres sql query error #16

Comments

matthiasgasser commented Jan 28, 2019

Apollon77 commented Jan 28, 2019

Apollon77 commented Jan 29, 2019

matthiasgasser commented Jan 29, 2019

Apollon77 commented Jan 29, 2019

matthiasgasser commented Jan 29, 2019

Apollon77 commented Jan 29, 2019

foxthefox commented Jan 30, 2019

Apollon77 commented Jan 30, 2019

holger05 commented Jan 31, 2019

Apollon77 commented Jan 31, 2019

holger05 commented Jan 31, 2019 • edited

2019-01-31 16:36:00.207 - info: phantomjs.0 Create 1200px*800px in 10000ms - "http://192.168.178.63:8082/vis/index.html#Wetter_Ext" => "/opt/iobroker/node_modules/iobroker.phantomjs/wetter-ext.png"

Apollon77 commented Jan 31, 2019

foxthefox commented Jan 31, 2019 • edited

holger05 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

holger05 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

holger05 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 1, 2019

holger05 commented Feb 1, 2019

robig commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

Apollon77 commented Feb 1, 2019

robig commented Feb 4, 2019

Apollon77 commented Feb 4, 2019

Apollon77 commented Feb 4, 2019

robig commented Feb 5, 2019

Apollon77 commented Feb 5, 2019

holger05 commented Jan 31, 2019 •

edited

foxthefox commented Jan 31, 2019 •

edited