How to use datetime column as a time series in Grafana using Postgres

Question

Hi guys I have a table in Postgres basically storing a door time entry. for example everytime you scan a card to open a door we store the datetime and your id table format

CREATE TABLE doorentry(date CHARACTER VARYING(50),id VARCHAR(255))

dataset looks like this

[{ "id" : "aadams1", "date" : "09-10-2022-14:55:30"},{ "id" : "jjames2", "date" : "09-10-2022-14:55:31"}]

I would like to group the dataset by their timestamp per hour. I have sort of achieved that with

SELECT SUBSTRING(date,0,16) AS byhour, COUNT(id)
FROM doorentry GROUP BY byhour ORDER BY byhour;

that looks something like this enter image description here

Would appreciate any help showing this data as a time series graph/new query to be able to use Grafana drop down to show data for the last day or hour like this: enter image description here

Jan Garaj · Accepted Answer · 2022-09-10 20:31:37Z

9

Use:

SELECT
  $__timeGroupAlias(<TIMESTAMPZ column>, '1h'),
  count(id) AS "count"
FROM doorentry
WHERE
  $__timeFilter(<TIMESTAMPZ column>)
GROUP BY 1
ORDER BY 1

But you don't have TIMESTAMPZ type in date, but VARCHAR. I would recommend to use TIMESTAMPZ for date column. With VARCHAR you have to convert varchar to timestamp first with SQL, e.g. TO_TIMESTAMP(date, 'MM-DD-YYYY HH:MI:SS').

See doc for available macros: https://grafana.com/docs/grafana/latest/datasources/postgres/#macros Those macros generate more complex SQL under the hood.

answered Sep 10, 2022 at 20:31

Jan Garaj

29k3 gold badges49 silver badges73 bronze badges

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

1 Comment

ak97 Over a year ago

Thank you, I have actually just set the date table to TIMESTAMP I know it won’t have a time zone but everything works.

Collectives™ on Stack Overflow

How to use datetime column as a time series in Grafana using Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related