Add the time range variable to (postgresql) query (in the panel)

Grafana Dashboards
, , ,
1

  • What Grafana version and what operating system are you using?
    I am using Grafana * v9.3.6 on Windows 10.

  • What are you trying to achieve?
    I aim to display the count of certain records using a panel visualization. I successfully ran the PostgreSQL query to fetch the count of the records. I have to now apply a specific type of time range filter to the query to allow the user to filter records during whichever time period they wish to choose.

  • How are you trying to achieve it?
    The PostgreSQL query worked for a hardcoded time range, I have pasted the specific line of my query:
    between (‘01-Jan-2023’) and (‘15-Feb-2023’)

image
image856×204 12.9 KB

I now want to extract the time selected by the user and place it instead of the hardcoded data values(which I guess will be done using global variables ${__from} and ${__from}.

to achieve the specific date format, I altered the query as:

between ‘${__from:date:DD-MMM-YYY}’ and ‘${__to:date:DD-MMM-YYY}’

  • What happened?
    However the results that were displayed for the hardcoded values are not visible now. I was using a gauge visualisation and the numerical values I could see before are now all set to 0. There is no error. Just the number 0 is displayed.

  • What did you expect to happen?
    I expected it to display the same values as before (when I used the hardcoded date)

  • Can you copy/paste the configuration(s) that you are having problems with?

image
image704×321 12.8 KB

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No errors received.

2

@saymeowmeow in your hardcoded example, it looks like your date is in format DD-MMM-YYYY, but your variable shows only 'YYY`.

Depending on what your data looks like, you might also check out these supported date macros for the postgres datasource:

1 Like
3

Thank you! It was resolved when I used the following variable format in my postgreSQL query:
BETWEEN ‘${__from:date:YYYY-MM-DD}’ and ‘${__to:date:YYYY-MM-DD}’

I just had to correctly match it to the date time format in my table

1 Like