Timezone mixup between software, PostgreSQL and Grafana

What Grafana version are you using?

What datasource are you using?

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:
timedate AS “time”,
FROM sp_temp

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.

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)?

This probably means that you put the timestamp in the database in local time. Timestamps in the db must always be in UTC.

so it’s an bug in the software? Because I tried to discuss this with the company and they didn’t cared about it. :unamused:

It’s simply a requirement of using Grafana. Database timestamps must be in
UTC, so either you ensure that’s the case, or you use somethng which doesn’t
have that requirement.

You can’t say it’s a bug in the code which is putting the data into your data
store any more than you can say it’s a bug in Grafana; it’s just part of the
overall system requirements, and if the front-end and back-end don’t agree on
that, there’s problem.


What s/w are you using to write the data to the database?
One solution if you can’t do anything about that would be to have a regular query which runs on the database and add a new column to each record with the UTC timestamp.

It’s a facility management software which uses a postgresql or an MS SQL as a Database.

And yes, clanlaw, “This probably means that you put the timestamp in the database in local time.” is the right conclusion of this issue. I have now changed the datatype for the column from “timestamp without time zone” to “timstamp with time zone”. Now I see in the DB the +1 after the date/time. It seems, that the Facility management software did not recognize the change and it still works like expected. At least Grafana is now happy with the information :slight_smile:

I’ll have to do some more test in my test environment with the Database and the facility management software befor I deploy it into production.

Oh, I thought you could not control that. So it appears the software is giving the db the right time after all, it is the fact that you told the db to ignore the timezone info in the passed in timestamp that caused the problem.