Hello, it’s amazing I got anything to display at all in Grafana. I am a total query n00b.
I have a php script logging data and correct local timestamps in a MySQL database.
I have a simple query in Grafana:
SELECT
Timestamp AS “time”,
Watts
FROM SolarEberlein
Order by Timestamp
For some reason, the entries only show up at the correct times if I select UTC as my timezone. Why?
It’s almost as if Grafana assumes my timestamps are Unix.
Hello, it’s amazing I got anything to display at all in Grafana. I am a
total query n00b. I have a php script logging data and correct local
timestamps in a MySQL database.
Oh dear - what do you mean by “local timestamps”?
If they’re not in UTC, you’ll have problems…
I have a simple query in Grafana:
SELECT Timestamp AS “time”, Watts FROM SolarEberlein Order by Timestamp
For some reason, the entries only show up at the correct times if I select
UTC as my timezone. Why?
Because Grafana requires timestamps stored in the database to be in UTC.
It’s almost as if Grafana assumes my timestamps are Unix.
Yes.
Store your timestamps in UTC and you won’t have these problems (and you won’t
have even more problems when the clocks change, either).
Regards,
Antony.
You may be able to work around this with MySQL by using the MySQL function UNIX_TIMESTAMP:
select UNIX_TIMESTAMP(datetime)*1000 as 'Date/Time'
Another alternative is to use the MySQL function CONVERT_TZ:
select CONVERT_TZ(datetime, 'SYSTEM', 'UTC') as 'Date/Time'
I use it successfully with DATETIME types anyway converting from London timezone to UTC.
Just note that before you can use CONVERT_TZ you do have to populate the timezone tables, for me in the UK I did this but what you need may be different:
mysql_tzinfo_to_sql /usr/share/zoneinfo/GB "Europe/London" |sudo mysql -u root -p mysql
mysql_tzinfo_to_sql /usr/share/zoneinfo/UTC "UTC" |sudo mysql -u root -p mysql
And if you have any timezone changes in future in your region like daylight savings time changes then things could be wrong until you update the timezone tables again.
