I have a Database with Events in it. Each event has a Unix Timestamp (int(11) unsigned in MySQL). Now in Grafana I want to display some Graphs e.g. with an Average of all events grouped on daily basis or, maybe as a more simple example, count all Events from one Day. Coming from Redash which I used before I tried to simply copy the queries from there (Had to adjust some things) and came to these 2 Queries:
SELECT sum(*) AS Total
WHERE DATE_FORMAT(from_unixtime(TIMESTAMP),’%Y-%j’) = DATE_FORMAT(NOW(),’%Y-%j’);
The first query is supposed to set the Timezone of the MySQL connection, the second is to fetch the count of today. The problem is that sometimes the second query seems to be executed first. When I hit reload a few times, sometimes I get the right results (Today defined as Today in UTC +5:30) and sometimes I get wrong results (Today defined as Today in UTC). Does that mean the order of the queries in Grafana is not necessarily the order of execution? If so, how could I ensure the MySQL Connection is using the correct timezone? (Without setting the timezone in MySQL Globally as there might be other applications using the same server using different timezones so the MySQL Timezone should remain UTC for default)
Looking at this time column requirements in Grafana I think I’m doing something basically wrong here but it seems so complicated while the query itself would be rather simple.
Thanks for any help