Grafana grafiek: Data loopt 2 uur achter

[ETH0.1]

Probleem: Grafana geeft niet de data weer maar loopt 'achter'

Ik heb in een MySQL database mijn stroomverbruik staan
datetime type: datetime
currentusage type: decimal 5,3 (dit is een berekende waarde over 5 minuten, maar de data is hier even niet belangrijk.)

van 08:00 tot 08:30 heb ik een verbruik van boven de 1kwh zoals ook te zien in in de MySQL tabel


Dataselectie in de grafiek is middels deze query:

code:

SELECT
  UNIX_TIMESTAMP(datetime) as time_sec,
  CurrentUsage as value,
  location as metric
FROM `Power-SUM`
WHERE $__timeFilter(datetime) 
ORDER BY datetime ASC

Selecteer ik vervolgens de grafiek 'Today so far' dan zie ik een grafiek verschijnen van 00:00 tot 09:35 (huidige tijd), maar de datalijn stopt om 07:35


Dit zelfde zie ik als ik 'last 3, 6, 12 hours etc' selecteer, het verbruik van 08:00 tot 08:30 is dus niet zichtbaar in deze grafieken, terwijl ik wel verwacht dat ze gewoon door lopen

enkels als ik de grafiek: 'today' selecteer zie ik de data verschijnen.


Ik denk dat het ergens mis gaat bij de dataselectie of dat de data verkeerd in de tabel staat (had dit bv in UTC weggeschreven moeten worden ipv CET?). want de grafieken lopen 2 uur achter op mijn verwachting.

Wie kan me de juiste weg in helpen?

Overigens draai ik Grafana 5.3.1

Bedankt

[BoAC]

De tijdzone van je bronnen staan goed? En tijdzone op je server ook?
Volgens deze pagina kun je de tijdzone instellen. Hoe dat zie ik niet

En als ik in de source zoek wordt er met tijdzones rekening gehouden.

[Voor 9% gewijzigd door BoAC op 20-10-2018 10:17]

[RobIII]

Grafana gaat standaard uit van UTC dus daar zal je probleem inderdaad (ergens) zitten.

ETH0.1 schreef op zaterdag 20 oktober 2018 @ 09:46:
(had dit bv in UTC weggeschreven moeten worden ipv CET?)

Het is sowieso slim om data of altijd weg te schrijven in UTC of inclusief tijdzone (offset); doe je dat niet dan krijg je wat je nu hebt. En wat je dan ook kiest: stick with it. Ga je 't mixen dan wordt 't (weer) een puinhoop.

[Voor 80% gewijzigd door RobIII op 20-10-2018 11:36]

[Jk_W]

Grafana zegt zelf het volgende over UTC:

UTC is a fairly standard setting for production monitoring, and I'm afraid we'll have a hard time finding a consensus on this issue so there's really no "good" setting for everyone.

However, you can easily change the time zone for your dashboards when logged-in to grafana. Simply click the cog icon, navigate to the General tab and adjust the Timezone drop-down.

[Bolukan]

De kern heeft RobIII al gezegd: mix niet, of bedenk heel goed wat je doet.

Je Datetime ziet er local uit (9:22 laatste record). Een Timestamp is iets anders en wordt opgeslagen met de secondes vanaf 1970 UTC-time. Als je een date meegeeft aan UNIX_TIMESTAMP wordt de timezone van de server gebruikt voor eventuele benodigde correctie.

Ik gok dat in Grafana de huidige tijd verkeerd wordt omgezet en de "tot nu" grafiek daarom verkeerd afkapt, terwijl de data er wel is "hele dag grafiek". Ik zou eerst in Grafana zoeken.

Even een citaat:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

[ETH0.1]

RobIII schreef op zaterdag 20 oktober 2018 @ 11:29:
Grafana gaat standaard uit van UTC dus daar zal je probleem inderdaad (ergens) zitten.

[...]

Het is sowieso slim om data of altijd weg te schrijven in UTC of inclusief tijdzone (offset); doe je dat niet dan krijg je wat je nu hebt. En wat je dan ook kiest: stick with it. Ga je 't mixen dan wordt 't (weer) een puinhoop.

En toen was het helemaal feest
2de datetime colom gemaakt datetime_UTC

via php de datatime uit de database gehaald en deze omgezet naar UTC

code:

date_default_timezone_set('Europe/Amsterdam');
..
$datetime = new DateTime("$database_select_datetime");
$utc_time = new DateTimeZone('UTC');
$datetime->setTimezone($utc_time);
$utc=$datetime->format('Y-m-d H:i:s');

Voorbeeld:
$datetime = 2018-10-21 18:00:00
$utc = 2018-10-21 16:00:00

deze waardes dus weer weggeschreven naar datetime_UTC, en vervolgens de dataselectie voor degrafiek aangepast. (ik heb voor type datetime gekozen omdat datetime geen timezone toevoeging heeft terwijl timstamp dit wel had, datetime verwacht UTC had ik begrepen)

resultaat was teleurstellend. rapport 'last 6 hours'
laat een plot zien voor data tussen 12:00 en 18:00, met alleen data tussen 12:00 en 14:00

Dat was dus niet de oplossing

SQL query logging aangezet

'today' vraagt om de volgende data selectie:

code:

21 Query     SELECT
  UNIX_TIMESTAMP(datetime_UTC) as time_sec,
  CurrentUsage as value,
  location as metric
FROM `Power-SUM`
WHERE datetime_UTC BETWEEN '2018-10-20T22:00:00Z' AND '2018-10-21T21:59:59Z'
ORDER BY datetime_UTC ASC

(doe maar alle data van vandaag, ook voor de uren in de toekomst, waarschijnlijk klopt de grafiek na 22:00 niet meer, maar dit heb ik niet gecontroleerd)

'last 6 hours' deze query heeft

code:

23 Query     SELECT
  UNIX_TIMESTAMP(datetime_UTC) as time_sec,
  CurrentUsage as value,
  location as metric
FROM `Power-SUM`
WHERE datetime_UTC BETWEEN '2018-10-21T10:08:10Z' AND '2018-10-21T16:08:10Z'
ORDER BY datetime_UTC ASC

Het lijkt er op alsof MySQL er vanuit gaat dat '2018-10-21T16:08:10Z' vanuit tijdzone GMT+2 wordt gedaan, deze zelf omzet naar 2018-10-21 14:08:10 (UTC dus), en dus krijg ik alleen de data te zien tussen 12:00 en 14:00.

MySQL geeft in de select query vervolgens wel de juiste datum terug, dat wordt niet geconverteerd (dus 13:30 in de database verschijnt zo ook in de grafiek)

Een quick and dirty fix heb ik nu even omdat ik 2 datetime tabellen heb kan ik daar ook gebruik van maken

code:

SELECT
  UNIX_TIMESTAMP(datetime) as time_sec,
  CurrentUsage as value,
  location as metric
FROM `Power-SUM`
WHERE $__timeFilter(datetime_UTC) 
ORDER BY datetime ASC

ik selecteer dus de data uit datetime (welke gmt+2 is), met een WHERE filter over datetime_UTC, dit geeft wel de juiste resultaten met een volledig gevulde grafiek, maar netjes is het niet. daar moet ik nog even aan werken en wat meer testen uitvoeren

[DaCoTa]

Alles op UTC zetten geeft uiteindelijk de minste hoofdpijn. Dat betekend dat de bron het dus al in UTC om moet zetten en opsturen. Dat is handig, want alleen de bron hoeft dan te weten waar en wanneer hij is. De andere kant (jouw browser) doet dan het tegenovergestelde, want die weet ook waar en wanneer die is.

[Bolukan]

datetime is niet persé UTC, timestamp (in secondes) wel.
Dus Grafana corrigeert nu waarschijnlijk nog een keer 2 uur op datetime_utc, want Grafana ruikt niet dat je een correctie hebt gedaan.

Probeer je datetime eens om te zetten in timestamp_utc (of zonder _utc het gaat om het timestamp format) en dan deze in Grafana in te brengen

code:

$__timefilter(timestamp_utc)

of

code:

$__timefilter(UNIX_TIMESTAMP(datetime))

Probleem lijkt mij niet te zitten in je data, maar in je timezone configuratie van Grafana.

http://docs.grafana.org/features/datasources/mysql/

[Voor 18% gewijzigd door Bolukan op 22-10-2018 12:17]

[DJMaze]

Het is zo simpel als je weet hoe het allemaal werkt.

Begin eens in MySQL met

SQL:

SET time_zone = '+0:00'

En gooi phpMyAdmin overboord en gebruik even een echt programma.

[.oisyn]

Bolukan schreef op maandag 22 oktober 2018 @ 12:04:
datetime is niet persé UTC, timestamp (in secondes) wel.

Een timestamp representeert een tijddstip. Dat is niet UTC of welke tijdzone dan ook

[Voor 8% gewijzigd door .oisyn op 22-10-2018 14:50]

[ETH0.1]

DJMaze schreef op maandag 22 oktober 2018 @ 14:04:
Het is zo simpel als je weet hoe het allemaal werkt.

Begin eens in MySQL met

SQL:

SET time_zone = '+0:00'

En gooi phpMyAdmin overboord en gebruik even een echt programma.

code:

mysql> SET time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `Power-SUM` where id=13655;
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
| id    | location | datetime            | datetime_UTC        | CurrentUsage | DaytimeTotal | NighttimeTotal | GasTotal |
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
| 13655 | MK-MM3   | 2018-10-23 17:17:30 | 2018-10-23 15:17:30 |        0.356 |     1414.495 |       1563.474 | 1488.422 |
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
1 row in set (0.00 sec)

* dit is het laatste record wat is toegevoegd aan de database


datetime_UTC is dus ook daadwerkelijk in UTC weggeschreven *datetime negeren we hier eventjes*

edit: alhoewel.....

code:

mysql> SET time_zone = '+2:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `Power-SUM` where id=13655;
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
| id    | location | datetime            | datetime_UTC        | CurrentUsage | DaytimeTotal | NighttimeTotal | GasTotal |
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
| 13655 | MK-MM3   | 2018-10-23 17:17:30 | 2018-10-23 15:17:30 |        0.356 |     1414.495 |       1563.474 | 1488.422 |
+-------+----------+---------------------+---------------------+--------------+--------------+----------------+----------+
1 row in set (0.00 sec)

MySQL lijkt hier niet op te reageren?

[Voor 27% gewijzigd door ETH0.1 op 23-10-2018 17:25]

[DJMaze]

ETH0.1 schreef op dinsdag 23 oktober 2018 @ 17:21:
MySQL lijkt hier niet op te reageren?

https://dev.mysql.com/doc...en/timezone-problems.html

SQL:

SET time_zone = '+2:00';
INSERT INTO table (datetime) VALUES ( NOW() );
INSERT INTO table (datetime) VALUES ( '2018-01-01 12:00' );

SET time_zone = '+0:00';
INSERT INTO table (datetime) VALUES ( NOW() );
INSERT INTO table (datetime) VALUES ( '2018-01-01 12:00' );

[Voor 57% gewijzigd door DJMaze op 23-10-2018 19:54]

[ETH0.1]

DJMaze schreef op dinsdag 23 oktober 2018 @ 19:51:
[...]

https://dev.mysql.com/doc...en/timezone-problems.html

SQL:

SET time_zone = '+2:00';
INSERT INTO table (datetime) VALUES ( NOW() );
INSERT INTO table (datetime) VALUES ( '2018-01-01 12:00' );

SET time_zone = '+0:00';
INSERT INTO table (datetime) VALUES ( NOW() );
INSERT INTO table (datetime) VALUES ( '2018-01-01 12:00' );

Ik ging er vanuit dat de timezone setting je select data aan zou passen,
Dus als het veld de waarde heeft 2018-01-01 12:00
en ik doe een select met timezone +00:00 dat er 2018-01-01 12:00 terug komt
en als de timezone op +2 zou staan dat er 2018-01-01 14:00

Maar dat is niet zo,
Bij het vullen van de tabel met NOW, wordt de tijd teruggerekend naar UTC, als je handmatig een tijd ingeeft blijft die zo staan.

[Bolukan]

Omdat dit mijn favoriete topic van het moment is, een kleine bijdrage met de output van bovenstaande queries:

code:

2018-10-24 16:31:11
2018-01-01 12:00:00

2018-10-24 14:31:11
2018-01-01 12:00:00

En om verder te spelen: DB Fiddle
PS: De 'rechter' query wordt altijd in UTC gedraaid. Het is me niet gelukt dat aan te passen.

[Voor 32% gewijzigd door Bolukan op 24-10-2018 17:02]

[chphe]

TLDR; MySQL doet in de functies UNIX_TIMESTAMP() en FROM_UNIXTIME() een timezone conversie.

Oplossingen zitten in het gebruik van CONVERT_TZ() of manueel op- aftrekken (bis, tris), set time_zone voor de sessie of in /etc/mysql/my.cnf voor een Grafana user de session timezone forceren.

Ter referentie voeg ik nog enkele bevindingen toe, welke ik vond toen ik tegen een gelijkaardig probleem liep.

• Post die bij mij het ah-moment opleverde: https://community.grafana...e-2-hours-offset/18418/16
• Meer over MySQL timezone support: https://dev.mysql.com/doc...en/time-zone-support.html
• Grafana 'bug': https://github.com/grafana/grafana/issues/15777
• Voor de CONVERT_TZ() functie kan je de parameters; @@global.time_zone, @@session.time_zone of SYSTEM gebruiken

[Creepy]

Maar om daar nu een topic uit 2018 voor te kicken is ook weer een beetje overdreven......