Graph in Grafana using Postgres Datasource with BIGINT column as time

Question

I'm trying to construct very simple graph showing how much visits I've got in some period of time (for example for each 5 minutes). I have Grafana of v. 5.4.0 paired well with Postgres v. 9.6 full of data. My table below:

CREATE TABLE visit (
    id                    serial CONSTRAINT visit_primary_key PRIMARY KEY,
    user_credit_id        INTEGER NOT NULL REFERENCES user_credit(id),
    visit_date            bigint NOT NULL,
    visit_path            varchar(128),
    method                varchar(8) NOT NULL DEFAULT 'GET'
);

Here's some data in it:

 id | user_credit_id |  visit_date   |                 visit_path                  | method 
----+----------------+---------------+---------------------------------------------+--------
  1 |              1 | 1550094818029 | /                                           | GET
  2 |              1 | 1550094949537 | /mortgage/restapi/credit/{userId}/decrement | POST
  3 |              1 | 1550094968651 | /mortgage/restapi/credit/{userId}/decrement | POST
  4 |              1 | 1550094988557 | /mortgage/restapi/credit/{userId}/decrement | POST
  5 |              1 | 1550094990820 | /index/UGiBGp0V                             | GET
  6 |              1 | 1550094990929 | /                                           | GET
  7 |              2 | 1550095986310 | /                                           | GET
...

So I tried these 3 variants (actually, dozens of others with no luck) with no success:

Solution A:

SELECT
  visit_date as "time",
  count(user_credit_id) AS "user_credit_id"
FROM visit
WHERE $__timeFilter(visit_date)
ORDER BY visit_date ASC

No data on graph. Error: pq: invalid input syntax for integer: "2019-02-14T13:16:50Z"

Solution B

SELECT
  $__unixEpochFrom(visit_date),
  count(user_credit_id) AS "user_credit_id"
FROM visit
GROUP BY time
ORDER BY user_credit_id

Series ASELECT
  $__time(visit_date/1000,10m,previous),
  count(user_credit_id) AS "user_credit_id A"
FROM
  visit
WHERE
 visit_date >= $__unixEpochFrom()::bigint*1000 and
 visit_date <= $__unixEpochTo()::bigint*1000
GROUP BY 1
ORDER BY 1

No data on graph. No Error..

Solution C:

SELECT
  $__timeGroup(visit_date, '1h'),
  count(user_credit_id) AS "user_credit_id"
FROM visit
GROUP BY time
ORDER BY time

No data on graph. Error: pq: function pg_catalog.date_part(unknown, bigint) does not exist

Could someone please help me to sort out this simple problem as I think the query should be compact, naive and simple.. But Grafana docs demoing its syntax and features confuse me slightly.. Thanks in advance!

Jan Garaj · Accepted Answer · 2019-02-15 11:34:21Z

1

Use this query, which will works if visit_date is timestamptz:

SELECT
  $__timeGroupAlias(visit_date,5m,0),
  count(*) AS "count"
FROM visit
WHERE
  $__timeFilter(visit_date)
GROUP BY 1
ORDER BY 1

But your visit_date is bigint so you need to convert it to timestamp (probably with TO_TIMESTAMP()) or you will need find other way how to use it with bigint. Use query inspector for debugging and you will see SQL generated by Grafana.

answered Feb 15, 2019 at 11:34

Jan Garaj

29k3 gold badges49 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikolay Chernov Over a year ago

Thanks a lot! Finally, with your advice I made a solution of my problem, see an answer below.

Nikolay Chernov · Accepted Answer · 2019-02-16 19:39:44Z

Jan Garaj, Thanks a lot! I should admit that your snippet and what's more valuable your additional comments advising to switch to SQL debugging dramatically helped me to make my "breakthrough". So, the resulting query which solved my problem below:

SELECT
  $__unixEpochGroup(visit_date/1000, '5m') AS "time",
  count(user_credit_id) AS "Total Visits"
FROM visit
WHERE
  '1970-01-01 00:00:00 GMT'::timestamp + ((visit_date/1000)::text)::interval BETWEEN
  $__timeFrom()::timestamp
  AND
  $__timeTo()::timestamp
GROUP BY 1
ORDER BY 1

Several comments to decypher all this Grafana magic:

Grafana has its limited DSL to make configurable graphs, this set of functions converts into some meaningful SQL (this is where seeing "compiled" SQL helped me a lot, many thanks again).
To make my BIGINT column be appropriate for predefined Grafana functions we need to simply convert it to seconds from UNIX epoch so, in math language - just divide by 1000.
Now, WHERE statement seems not so simple and predictable, Grafana DSL works different where and simple division did not make trick and I solved it by using another Grafana functions to get FROM and TO points of time (period of time for which Graph should be rendered) but these functions generate timestamp type while we do have BIGINT in our column. So, thanks to Postgres we have a bunch of converter means to make it timestamp ('1970-01-01 00:00:00 GMT'::timestamp + ((visit_date/1000)::text)::interval - generates you one BIGINT value converted to Postgres TIMESTAMP with which Grafana deals just fine).

P.S. If you don't mind I've changed my question text to be more precise and detailed.

Collectives™ on Stack Overflow

Graph in Grafana using Postgres Datasource with BIGINT column as time

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related