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

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

CodePudding user response：

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.