1

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

ak97
  • 15
  • 1
  • 6

1 Answers1

7

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.

Jan Garaj
  • 25,598
  • 3
  • 38
  • 59
  • Thank you, I have actually just set the date table to TIMESTAMP I know it won’t have a time zone but everything works. – ak97 Sep 13 '22 at 01:32