What Grafana version are you using?
6.6.2
What datasource are you using?
PostgreSQL
What OS are you running grafana on?
windows 10
What did you do?
I just want to see the last xx hours correctly. In my Dashboard I have a Graph which should show me the information from a PostgreSQL Database.
What was the expected result?
Corect information on the correct timeline within the last 1 hour.
What happened instead?
At first it showed the values correctly in the graph but the x axis but an hour late (I am in UTC+1). Then I changed the browser to use UTC. Now I am getting the correct values in the correct time. But now I can’t see the missing last hour becuase the last hour is now -2 hours to -1 hours.
It’s an default query:
SELECT
timedate AS “time”,
“Value”
FROM sp_temp
WHERE
$__timeFilter(timedate)
ORDER BY 1
After searching for an answer I found out that the software which is adding data to the PostgreSQL Database is completly programmed wrong. They are using the date and time incl the timezone (in this example its now 10am incl UTC+1) and they write it into the column timedate which is a datatyp “timestamp wthout time zone”. So the database has now the information it’s 10am without UTC+1.
Example:
every machine is running in UTC+1 and it’s NOW 10:30 am
PostgreSQL Database has this:
timedate (timestamp without time zone); value;
2020-10-25 9:00:00; 1;
2020-10-25 9:15:00; 2;
2020-10-25 9:30:00; 3;
2020-10-25 9:45:00; 4;
2020-10-25 10:00:00; 5;
2020-10-25 10:15:00; 6;
2020-10-25 10:30:00; 7; <-- this is “NOW”
Grafana shows me in the graph with the above sql statement and last one hour
2020-10-25 10:00:00; 1;
2020-10-25 10:15:00; 2;
2020-10-25 10:30:00; 3;
changing the browser to UTC with same sql statement and last one hour:
2020-10-25 9:00:00; 1;
2020-10-25 9:15:00; 2;
2020-10-25 9:30:00; 3;
So no change to see the 4-7 values. Any idea how change the query, so that it asks for UTC Values but shows it as UTC+1 incl. the last 1 hour information (and not the information between -2 to -1)?
