Same query, different results

Grafana Dashboards
1
  • Grafana version 10.3.1

Hi all, I’m trying to group by day from a PostgreSQL timestamp. It is simple to do with PostgreSQL, however executing the right query in Grafana gives different results than usual.

I would like to understand what is going on here.

Here is the query Grafana says it is using:

image

Note the cast: “date_time::date” - this should return only the date part of date_time, which worked fine when I tested it in SQL Workbench:

image
image297×511 6.57 KB

That is the exact same query, copy and pasted from the Grafana query inspector.

Here are the results in Grafana:

image
image952×658 35.9 KB

Note that a timestamp is present now in the “day” column.

How is this possible? There are no Transformations on this panel. It’s just the query shown.

2

I think I figured it out…

Grafana has it’s own types which it automatically converts to. Seems the PostgreSQL “date” type gets automatically converted to Grafana’s “time” type.

There does not appear to be a date type to convert to in Grafana, so the solution here is to convert the PostgreSQL dates to strings:

to_char(date_time, 'YYYY-MM-DD')

Hope this helps someone

2 Likes