Hallo Sebastian,

vielen Dank für deine schnelle Antwort. Das Kommando funktioniert, aber dauert sehr lange:

MariaDB [siemens]> SELECT TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created DESC)) AS diff FROM log1 ORDER BY created DESC LIMIT 1; +------+ | diff | +------+ | -121 | +------+ 1 row in set (1 min 5.042 sec)

Da kann ich die beiden Zeitstempel deutlich schneller in PHP (wo das SQL benutzt wird) subtrahieren.

VG,

Erik

Am 19.02.22 um 13:29 schrieb Sebastian Weckend:

Hallo Erik,

schau dir mal die window functions [0], besonders LEAD [1] und LAG [2] an.

Wenn der Datentyp von created TIMESTAMP ist und du nur den Unterschied der beiden neuesten Zeilen in der DB möchtest, dann so was ähnliches wie (ungetestet):

SELECT     TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created DESC)) AS diff FROM table ORDER BY created DESC LIMIT 1;

Viele Grüße Sebastian

[0] https://mariadb.com/kb/en/window-functions/ [1] https://mariadb.com/kb/en/lead/ [2] https://mariadb.com/kb/en/lag/

Am 19.02.22 um 12:14 schrieb Erik Schanze:

...

Liebe Linux-User,

ich habe eine MariaDB auf Debian 11 laufen und möchte die Zeitdifferenz in Sekunden zwischen der letzten und der vorletzten Zeile in einer Tabelle haben, anhand des "created"-Wertes. Die Beispiele im Internet ufern irgendwie immer gleich derart aus, dass ich sie nicht mehr verstehe. Ich habe nur rudimentäres SQL-Wissen. :-)

Gibt es da kein einfache Abfrage, die ich nur nicht gefunden habe?

Ich freue mich auf eure Antworten.

VG,

Erik

Wahrscheinlich wäre es in dem Beispiel besser, ORDER BY id DESC in der query und der LAG function zu nutzen. Das funktioniert aber nur, wenn die Einträge auch in zeitlicher Reihenfolge geschrieben werden. Wäre aber spannend zu wissen.

VG Sebastian

Am 19.02.22 um 13:59 schrieb Sebastian Weckend:

Hallo Erik,

ich bin kein SQL Experte, aber bei großen Tabellen kann es evtl. schneller sein, wenn du mit den richtigen indices arbeitest. Ich kenne mich mit MariaDB nicht aus, aber da kann man sich sicher auch mit EXPLAIN anschauen, was die query macht und dann optimieren.

Ansonsten einfach die Werte der beiden neuesten Zeilen im PHP subtrahieren, aber das war ja nicht die Frage ;)

VG Sebastian

Am 19.02.22 um 13:52 schrieb Erik Schanze:

...

Hallo Sebastian,

vielen Dank für deine schnelle Antwort. Das Kommando funktioniert, aber dauert sehr lange:

MariaDB [siemens]> SELECT TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created DESC)) AS diff FROM log1 ORDER BY created DESC LIMIT 1; +------+ | diff | +------+ | -121 | +------+ 1 row in set (1 min 5.042 sec)

Da kann ich die beiden Zeitstempel deutlich schneller in PHP (wo das SQL benutzt wird) subtrahieren.

VG,

Erik

Am 19.02.22 um 13:29 schrieb Sebastian Weckend:

...

Hallo Erik,

schau dir mal die window functions [0], besonders LEAD [1] und LAG [2] an.

Wenn der Datentyp von created TIMESTAMP ist und du nur den Unterschied der beiden neuesten Zeilen in der DB möchtest, dann so was ähnliches wie (ungetestet):

SELECT     TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created DESC)) AS diff FROM table ORDER BY created DESC LIMIT 1;

Viele Grüße Sebastian

[0] https://mariadb.com/kb/en/window-functions/ [1] https://mariadb.com/kb/en/lead/ [2] https://mariadb.com/kb/en/lag/

Am 19.02.22 um 12:14 schrieb Erik Schanze:

...

Liebe Linux-User,

ich habe eine MariaDB auf Debian 11 laufen und möchte die Zeitdifferenz in Sekunden zwischen der letzten und der vorletzten Zeile in einer Tabelle haben, anhand des "created"-Wertes. Die Beispiele im Internet ufern irgendwie immer gleich derart aus, dass ich sie nicht mehr verstehe. Ich habe nur rudimentäres SQL-Wissen. :-)

Gibt es da kein einfache Abfrage, die ich nur nicht gefunden habe?

Ich freue mich auf eure Antworten.

VG,

Erik

Deswegen dauert es wahrscheinlich so lang, da ohne Index für die Sortierung alle Zeilen angeschaut werden müssen. Entweder solltest du für den Timestamp einen Index hinzufügen (was den aber unique machen dürfte, also u.U. nicht gewollt) oder einen primary key hinzufügen, nach dem du sortieren kannst.

VG Sebastian

Am 19.02.22 um 14:47 schrieb Erik Schanze:

Am 19.02.22 um 14:08 schrieb Sebastian Weckend:

...

Wahrscheinlich wäre es in dem Beispiel besser, ORDER BY id DESC in der query und der LAG function zu nutzen. Das funktioniert aber nur, wenn die Einträge auch in zeitlicher Reihenfolge geschrieben werden. Wäre aber spannend zu wissen.

Ich habe keine Spalte 'id', sondern nur:

MariaDB [siemens]> SELECT * FROM log1 ORDER BY created DESC LIMIT 2; +---------------------+----------+----------+ | created             | c180     | c280     | +---------------------+----------+----------+ | 2022-02-18 23:58:10 | 11791536 | 17142137 | | 2022-02-18 23:56:09 | 11791523 | 17142137 | +---------------------+----------+----------+ 2 rows in set (0.001 sec)

Ich werds in PHP lösen, danke trotzdem.

VG,

Erik

Am 19.02.22 um 15:21 schrieb Erik Schanze:

Am 19.02.22 um 14:52 schrieb Sebastian Weckend:

...

Deswegen dauert es wahrscheinlich so lang, da ohne Index für die Sortierung alle Zeilen angeschaut werden müssen. Entweder solltest du für den Timestamp einen Index hinzufügen (was den aber unique machen dürfte, also u.U. nicht gewollt) oder einen primary key hinzufügen, nach dem du sortieren kannst.

Sorry, da bin ich schon raus. ;-)

Der Anwendungsfall hier ist ein Logger, der alle 2 Minuten Stromzählerwerte in die Datenbank schreibt. Zum Zugriff auf die Tabellenwerte wird immer der Timestamp genutzt, also alle Werte eines bestimmten Tages oder eines Monats etc. aus der Datenbank geholt und dann in PHP in Diagrammen dargestellt.

Wie hilft mir da ein Index? Weitere Spalten wollte ich vermeiden, um die Datenmenge nicht unnötig aufzublähen.

Wenn Du alle Werte eines Tages holen willst, hilft ein Index schon mal ganz gewaltig. Ich hab das mal nachgestellt. PostgreSQL 14.1 in einer lokalen Minikube-Kubernetes-Instanz, daher nicht unbedingt schnell.

test=# create table logger(created timestamp, c180 bigint, c280 bigint); CREATE TABLE test=# insert into logger select '2000-01-01'::timestamp + s * '1minute'::interval, random()*100000, random()*100000 from generate_series(1,10000000) s; INSERT 0 10000000

test=# select min(created), max(created) from logger;          min         |         max ---------------------+---------------------  2000-01-01 00:01:00 | 2019-01-05 10:40:00 (1 row)

Das sind also 10.000.000 Datenpunkte, minütlicher Abstand, nach ca. 9 Jahren.

test=# explain analyse select created, c180, c180 - lag(c180) over (order by created), c280, c280 - lag(c280) over (order by created) from logger;                                                              QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------  WindowAgg  (cost=1736550.18..1986553.06 rows=10000115 width=40) (actual time=88513.167..225937.881 rows=10000000 loops=1)    ->  Sort  (cost=1736550.18..1761550.47 rows=10000115 width=24) (actual time=88513.112..131629.556 rows=10000000 loops=1)          Sort Key: created          Sort Method: external merge  Disk: 332768kB          ->  Seq Scan on logger  (cost=0.00..163696.15 rows=10000115 width=24) (actual time=0.132..43941.856 rows=10000000 loops=1)  Planning Time: 0.818 ms  Execution Time: 267222.240 ms (7 rows)

Man sieht, es dauert. Und: es muß zum Sortieren temp. auf Platte schreiben (Sort Method: external merge  Disk: 332768kB).

Jetzt mit Index:

test=# create index idx_logger on logger(created); CREATE INDEX test=# explain analyse select created, c180, c180 - lag(c180) over (order by created), c280, c280 - lag(c280) over (order by created) from logger; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------  WindowAgg  (cost=0.43..548386.44 rows=10000000 width=40) (actual time=0.302..142101.995 rows=10000000 loops=1)    ->  Index Scan using idx_logger on logger (cost=0.43..323386.43 rows=10000000 width=24) (actual time=0.270..45946.164 rows=10000000 loops=1)  Planning Time: 0.646 ms  Execution Time: 183889.371 ms (4 rows)

Das ist schon deutlich schneller.

Ich erstelle mal noch einen anderen Index, der die 2 Spalten als INCLUDE hat (PostgreSQL-Feature):

test=# create index idx_logger_include on logger(created) include (c180,c280); CREATE INDEX

Nun frage ich mal die Daten nur für einen Tag ab:

test=# explain analyse select created, c180, c180 - lag(c180) over (order by created), c280, c280 - lag(c280) over (order by created) from logger where created between '2007-07-28'::date and '2007-07-29'::date; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------  WindowAgg  (cost=0.43..86.96 rows=1377 width=40) (actual time=0.216..24.339 rows=1441 loops=1)    ->  Index Only Scan using idx_logger_include on logger (cost=0.43..55.97 rows=1377 width=24) (actual time=0.187..8.642 rows=1441 loops=1)          Index Cond: ((created >= '2007-07-28'::date) AND (created <= '2007-07-29'::date))          Heap Fetches: 0  Planning Time: 0.160 ms  Execution Time: 31.503 ms (6 rows)

Das dürfte für Dich realistischer sein. Nun lösche ich beide Indexe und führe letztere Abfrage noch mal aus:

test=# drop index idx_logger; DROP INDEX test=# drop index idx_logger_include test-# ; DROP INDEX test=# test=# test=# explain analyse select created, c180, c180 - lag(c180) over (order by created), c280, c280 - lag(c280) over (order by created) from logger where created between '2007-07-28'::date and '2007-07-29'::date;                                                                QUERY PLAN -----------------------------------------------------------------------------------------------------------------------------------------  WindowAgg  (cost=127220.61..127411.97 rows=1377 width=40) (actual time=336.972..356.114 rows=1441 loops=1)    ->  Gather Merge  (cost=127220.61..127380.98 rows=1377 width=24) (actual time=336.853..343.285 rows=1441 loops=1)          Workers Planned: 2          Workers Launched: 2          ->  Sort  (cost=126220.58..126222.02 rows=574 width=24) (actual time=330.211..332.998 rows=480 loops=3)                Sort Key: created                Sort Method: quicksort  Memory: 25kB                Worker 0:  Sort Method: quicksort  Memory: 25kB                Worker 1:  Sort Method: quicksort  Memory: 161kB                ->  Parallel Seq Scan on logger (cost=0.00..126194.28 rows=574 width=24) (actual time=261.502..328.286 rows=480 loops=3)                      Filter: ((created >= '2007-07-28'::date) AND (created <= '2007-07-29'::date))                      Rows Removed by Filter: 3332853  Planning Time: 0.106 ms  Execution Time: 361.985 ms (14 rows)

Du siehst, ohne Index ist die Abfrage Faktor 10 langsamer. Was hast Du an Mengen von Datensätzen, daß das bei Dir so lange dauert? (PostgreSQL führt die Abfrage auf 3 Cores aus, der Hauptprozess startet 2 Worker-Prozesse)

Btw: was mir grad auffällt, bei den gezeigten Lösungen bisher wird die Differenz der Zeitspalte berechnet, die aber eigentlich immer 2 Minuten sein sollte, nach Deinen Ausführungen. Ich habe die Differenz der Meßwerte zur vorherigen Messung berechnet, was ist richtig?

Andreas

Am 20.02.22 um 15:58 schrieb Erik Schanze:

Hallo Andreas,

vielen Dank für deine ausführliche Antwort.

gern ;-)

Am 20.02.22 um 14:13 schrieb Andreas Kretschmer:

...

Wenn Du alle Werte eines Tages holen willst, hilft ein Index schon mal ganz gewaltig. Ich hab das mal nachgestellt. PostgreSQL 14.1 in einer lokalen Minikube-Kubernetes-Instanz, daher nicht unbedingt schnell.

Angelegt wurde die Tabelle mit dem Kommando:

CREATE TABLE IF NOT EXISTS $tabelle (     created timestamp NOT NULL default CURRENT_TIMESTAMP,     c180 int(12) unsigned default NULL,     c280 int(12) unsigned default NULL,     PRIMARY KEY  (created)     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Die Spalte "created" ist bei der Tabelle schon als "primary key" eingestellt. Lt. Wikipedia wird da automatisch ein Index angelegt. Hilft da zusätzlich noch einer?

ja, dann ist da impliziet ein Index und du brauchst keinen weiteren.

...

Du siehst, ohne Index ist die Abfrage Faktor 10 langsamer. Was hast Du an Mengen von Datensätzen, daß das bei Dir so lange dauert? (PostgreSQL führt die Abfrage auf 3 Cores aus, der Hauptprozess startet 2 Worker-Prozesse)

2-minütliche Datensätze 11 Jahre lang. Der Logger ist ein Intel Atom Z520 (1 Core, 2 Threads, 1,33MHz) und über nimmt auch die Verarbeitung und Darstellung auf einer Webseite. Da braucht man Geduld. :-)

alles klar, da sind dann keine Wunder zu erwarten.

...

Btw: was mir grad auffällt, bei den gezeigten Lösungen bisher wird die Differenz der Zeitspalte berechnet, die aber eigentlich immer 2 Minuten sein sollte, nach Deinen Ausführungen. Ich habe die Differenz der Meßwerte zur vorherigen Messung berechnet, was ist richtig?

Ich brauche die Zeitdifferenz der letzten zur vorletzten Zeile in der Tabelle. Es sind nicht immer genau 2 min. Ich habe die Zeitdifferenzberechnung jetzt in PHP gelöst. Ich dachte, es gibt da einfache Time-Funktionen in (MY)SQL, weil der Timestamp ja von der Datenbank geschrieben wird.

ja, ich versteh. MySQL ist nicht wirklich technologisch gesehen der Bringer, solche Window-Funktionen gibt es da erst seit kurzer Zeit und das mag in PHP dann schneller sein.

So langsam wird mir wieder klar, warum ich seit dem Studium immer einen großen Bogen um Datenbanken gemacht habe. Sie helfen Probleme zu lösen, die wir ohne sie nicht hätten. ;-)

nun ja, ich verdiene mein Geld damit (aber nicht mit MySQL)

Andreas