Can not refer to postgresql data

[dzl84]

I tried to use postgresql data but it always shows no data or blank. How can I refer to the query result in the postgresql query?

[Gauravshah]

I think there has been issues with single stat. Can you try using table visualization ? will be easier to debug

[dzl84]

Thank you for the quick response. I changed to table visualization. Same issue.

[dzl84]

It looks like the query response does not have the data for the second part which is a meta-queries.

[Gauravshah]

@dzl84 I think I know the issue. Its related to the fact that its not timeseries data. GroupBys are currently not supported. If you update your query to be presented as timeseries and have a timecomponent it would work

[dzl84]

@Gauravshah thank you for the idea. I changed the query to be a timeseries and now I hit a new problem. I want to use 2 pg queries and then run a calc to count the pass rate. In query C, when I just set exp to A['pass'], it showed the correct value, but when I set it to A['pass'] + B['pass'], it then showed 0.

[Gauravshah]

Can you try looking at B[‘pass’] ? Also try renaming it to a different column. May be pass2

On Wed, 15 Jan 2020 at 7:49 AM, dzl84 notifications@github.com wrote:

@Gauravshah https://github.com/Gauravshah thank you for the idea. I changed the query to be a timeseries and now I hit a new problem. I want to use 2 pg queries and then run a calc to count the pass rate. In query C, when I just set exp to A['pass'], it showed the correct value, but when I set it to A['pass'] + B['pass'], it then showed 0. [image: Screen Shot 2020-01-15 at 10 18 24 AM] https://user-images.githubusercontent.com/1811343/72399212-6fd82800-3780-11ea-8275-04107a2418c8.png

[image: Screen Shot 2020-01-15 at 10 15 05 AM] https://user-images.githubusercontent.com/1811343/72399172-4d460f00-3780-11ea-87e9-1dbbeb9921fc.png

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/GoshPosh/grafana-meta-queries/issues/87?email_source=notifications&email_token=AAEXQD4O4O65AD7VJW7CCVTQ5ZXBRA5CNFSM4KGUR52KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEI62COQ#issuecomment-574464314, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEXQD5PEOK65WMWDFBSZMLQ5ZXBRANCNFSM4KGUR52A .

[dzl84]

either A['pass'] or B['pass'] works. But when I add them together it becomes 0.

[dzl84]

Also tried to rename to a different column, no luck.

[Gauravshah]

@dzl84 haven't come across that issue. If both A['pass'] & B['pass'] individually works then the sum should also work. Can you look at web console to see if there are any JS issues ?

[richardmh]

I'm having the same problem, this is a postgres-timescale db JS panel is showing in screenshot If the expression is A['x'] then values column is undefined, or, as here A['x']*2 values are NaN

[richardmh]

for info, the generated sql is this: SELECT time_bucket('5s',dt) AS "time", topic AS metric, sum(payload_num) as x FROM mqtt WHERE dt BETWEEN '2020-01-19T14:47:12.39Z' AND '2020-01-19T15:48:41.988Z' AND topic = 'iotaCelr/main_total/wh' GROUP BY 1,2 ORDER BY 1,2

[Gauravshah]

@richardmh your issue is different, your query is showing up on graph as 'iotaCelr/main_total/wh'

so doing A['iotaCelr/main_total/wh'] should fix it

[richardmh]

Aha! Works perfectly. I hadn't grasped that it was the metric rather than the alias of the value which was required. Thankyou, not a fault at all. Richard

[Gauravshah]

@richardmh it should ideally be the same, somehow the postgres changes the name. grafana-meta-queries sees data the way grafana sees it. So what shows in UI is what we get within A

[AlexandreGuidin]

Had the same issue here, A and B individually works

[AlexandreGuidin]

Actually, it prints two erros

i've figured out that the variables resultsHash does not contains value for both datasources at the exatctly same timestamp:

and then when it calls var data = resultsHash[datapointTime]; it contains values just from one datasource.

Even if Grafana show the values at the same minute/seconds, it have some milliseconds of difference

[AlexandreGuidin]

Fixed here.

In my case only matter the value of entire day, so i've changed the queries timestamp to "at the start of day"

mysql: UNIX_TIMESTAMP(CURDATE()) as time, postgres: current_date::timestamp at time zone 'America/Sao_Paulo' as time,

had other timezone diferences either...