mikrocontroller.net

Forum: PC-Programmierung [SQL] Timestamp auf volle Minuten runden / kürzen


Announcement: there is an English version of this forum on EmbDev.net. Posts you create there will be displayed on Mikrocontroller.net and EmbDev.net.
von Rene K. (xdraconix)


Bewertung
0 lesenswert
nicht lesenswert
Ich habe eine Datenbank in denen IP Adressen und Zeiten gespeichert 
sind, nun möchte ich die Zeiten auf volle Minuten runden oder kürzen, 
siehe dazu:

Die Abfrage lautet:
SELECT ip, time, COUNT(*) 
FROM hosts 
GROUP BY time

//gekürzt da mehrere millionen Einträge

   IP                  time              count
192.168.178.1   2020-01-18 16:28:44       11
192.168.178.1   2020-01-18 16:31:41       11
192.168.178.1   2020-01-18 16:40:07       10
192.168.178.1   2020-01-18 16:45:10       10
192.168.178.1   2020-01-18 16:50:05       10
192.168.178.1   2020-01-18 16:55:07       11
192.168.178.1   2020-01-18 17:00:16       11
192.168.178.1   2020-01-18 17:05:06       10
192.168.178.1   2020-01-18 17:10:07        7
192.168.178.43  2020-01-18 17:10:08        5
192.168.178.1   2020-01-18 17:15:10       12
192.168.178.1   2020-01-18 17:20:13       11
192.168.178.1   2020-01-18 17:23:27        3
192.168.178.27  2020-01-18 17:23:28        8
192.168.178.1   2020-01-18 17:25:08        8
192.168.178.46  2020-01-18 17:25:09        3
192.168.178.1   2020-01-18 17:30:07       11
192.168.178.1   2020-01-18 17:35:05        3
192.168.178.27  2020-01-18 17:35:06        8
192.168.178.1   2020-01-18 17:40:09       11
192.168.178.1   2020-01-18 17:45:09        1
192.168.178.20  2020-01-18 17:45:10       10
192.168.178.1   2020-01-18 17:50:08       11
192.168.178.1   2020-01-18 17:55:09       11
192.168.178.1   2020-01-18 18:00:09       10

Nun sieht man vereinzelt Einträge welche in gleichen Minute liegen, aber 
unterschiedliche Sekunden haben, diese gehören aber zusamnmen z.b. hier:
192.168.178.1   2020-01-18 17:45:09        1
192.168.178.20  2020-01-18 17:45:10       10

die IP Adresse spielt da erstmal keine Rolle, es geht um die Zeit. ich 
möchte nun entweder die Sekunden herraustrennen das alle auf 00 stehen 
oder aber noch besser für mich wäre ein Zeitfenster zu definieren z.b. 
+-3 Minuten in dem diese Zeit zusammengefasst ist.

Hat da jemand einen Anhaltspunkt für mich. Ich nutze MariaDB - so das 
etwas wie TIMESTAMP_TRUNC leider nicht funktioniert.

von imonbln (Gast)


Bewertung
0 lesenswert
nicht lesenswert
was ist mit der extract Funktion, die sollte machen was du willst.

https://mariadb.com/kb/en/extract/

von T.roll (Gast)


Bewertung
0 lesenswert
nicht lesenswert
SELECT DATE_FORMAT(SPALTE, '%d.%m.%Y %H:%i') AS SPALTE

von Rene K. (xdraconix)


Bewertung
0 lesenswert
nicht lesenswert
T.roll schrieb:
> DATE_FORMAT(SPALTE, '%d.%m.%Y %H:%i')

Das würde genau das machen was ich möchte, jedoch leider eine absolute 
Resourcenschleuder. Ein Auszug aus dieser Datenbank von ~2.500 
Datensätzen schlägt dann glatt so zu Buche:
(2555 insgesamt, Die Abfrage dauerte 16.8884 Sekunden.)

Das ist mit knapp 3.000.000 Datensätzen nicht mehr machbar. Worauf ich 
mich natürlich einlassen würden, Nachts einen Cronjob laufen zu lassen, 
welche sämtliche Daten aus dem jeweiligen Tag mit dieser Anweisung 
überbügelt und zusammenfasst. Ist aber nicht das worauf ich eigentlich 
hinaus möchte.

imonbln schrieb:
> was ist mit der extract Funktion, die sollte machen was du willst.
>
> https://mariadb.com/kb/en/extract/

Das werde ich mir mal näher anschauen.

von T.roll (Gast)


Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:
> Das würde genau das machen was ich möchte, jedoch leider eine absolute
> Resourcenschleuder.

Setz mal einen Index auf die Spalte.

von Egon D. (egon_d)


Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:

> Hat da jemand einen Anhaltspunkt für mich.

Wäre es akzeptabel, eine zweite Spalte anzulegen,
die den Zeitstempel des VORGÄNGERS enthält?
Dann müssten sich doch die betroffene Datensätze
über die -- zu kleine -- Differenz dieser beiden
Felder ermitteln lassen...

von c-hater (Gast)


Bewertung
1 lesenswert
nicht lesenswert
Rene K. schrieb:

> Nun sieht man vereinzelt Einträge welche in gleichen Minute liegen, aber
> unterschiedliche Sekunden haben, diese gehören aber zusamnmen z.b. hier:
>
>
> 192.168.178.1   2020-01-18 17:45:09        1
> 192.168.178.20  2020-01-18 17:45:10       10
> 
>
> die IP Adresse spielt da erstmal keine Rolle, es geht um die Zeit.

Wenn das so ist, darfst du die IP-Adresse natürlich nicht anzeigen 
lassen, sonst kannst du nicht (nur) nach der Zeit gruppieren.

> möchte nun entweder die Sekunden herraustrennen das alle auf 00 stehen
> oder aber noch besser für mich wäre ein Zeitfenster zu definieren z.b.
> +-3 Minuten in dem diese Zeit zusammengefasst ist.
>
> Hat da jemand einen Anhaltspunkt für mich. Ich nutze MariaDB - so das
> etwas wie TIMESTAMP_TRUNC leider nicht funktioniert.

Erstmal musst du uns einen Anhaltspunkt liefern. Nämlich: welchen 
Datentyp hat eigentlich die Spalte [time]?

von Rene K. (xdraconix)


Bewertung
0 lesenswert
nicht lesenswert
T.roll schrieb:
> Rene K. schrieb:
>> Das würde genau das machen was ich möchte, jedoch leider eine absolute
>> Resourcenschleuder.
>
> Setz mal einen Index auf die Spalte.

Getan, gut Idee.

c-hater schrieb:
> Wenn das so ist, darfst du die IP-Adresse natürlich nicht anzeigen
> lassen, sonst kannst du nicht (nur) nach der Zeit gruppieren.

Völlig korrekt, das gibt ja ein "logisches Wirrwarr". Das in Verbindung 
mit dem Index auf Spalte 'time' hat nun eine extremen Zeitsprung 
gemacht. Eine Abfrage von obigen Beispiel mit:
SELECT DATE_FORMAT(`time`, '%d.%m.%Y %H:%i'), COUNT(*) 
FROM hosts 
GROUP BY DATE_FORMAT(`time`, '%d.%m.%Y %H:%i')  

ist nun bei ca. 0.9 Sekunden.

c-hater schrieb:
> Erstmal musst du uns einen Anhaltspunkt liefern. Nämlich: welchen
> Datentyp hat eigentlich die Spalte [time]?

'time' ist vom Datentyp 'timestamp' und Auto-fill über 
'current_timestamp()  '.

Das wird auch der Punkt sein, wo ich zukünftig anpacken muss. Da die 
Daten über einen Cronjob mittels Script reinkommen, werde ich die Zeit 
wohl nicht mehr automatisch eintragen lassen, sondern werde sie manuell 
in die Tabelle einfügen lassen - da umgehe ich auch in Zukunft dieses 
Problem. Die aktuelle Tabelle werde ich umschreiben lassen.

: Bearbeitet durch User
von Rene K. (xdraconix)


Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:
> Das wird auch der Punkt sein, wo ich zukünftig anpacken muss. Da die
> Daten über einen Cronjob mittels Script reinkommen, werde ich die Zeit
> wohl nicht mehr automatisch eintragen lassen, sondern werde sie manuell
> in die Tabelle einfügen lassen - da umgehe ich auch in Zukunft dieses
> Problem. Die aktuelle Tabelle werde ich umschreiben lassen.

Hab das gleich mal geändert. So kommen erst gar keine falsche Daten in 
die Tabelle:
TimeStamp=$(python -c "from datetime import datetime; print datetime.utcnow().strftime('%Y-%m-%d %H:%M:00.00')[:-4]")

sudo nmap -sP 192.168.178.0/24  | tr -d '()' | awk -vtimest="$TimeStamp" '/Nmap scan report/{if($6=="") {$6=$5;$5="None"} print "INSERT INTO hosts_2 (ip,host,time,mac) VALUES(\""$6"\",\""$5"\",\""timest"\"";getline;getline;print",\""$3"\");";}' | mysql ....


von DB (Gast)


Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:
> Hab das gleich mal geändert. So kommen erst gar keine falsche Daten in
> die Tabelle:

Du könntest auch einen Datenbank-Trigger setzen, der anschlägt wenn ein 
neues Datum eingefügt wird und die Zeit dann anpasst, das wäre dann 
multi-client safe.

von Rene K. (xdraconix)


Bewertung
0 lesenswert
nicht lesenswert
DB schrieb:
> Du könntest auch einen Datenbank-Trigger setzen, der anschlägt wenn ein
> neues Datum eingefügt wird und die Zeit dann anpasst, das wäre dann
> multi-client safe.

Das muss nicht unbedingt Multi-client sicher sein, da es speziell auf 
den Server und für dieses Subnetz ist. Die Idee mit dem Trigger ist 
super, habe ich aber noch nicht gemacht (Trigger für SQL Events). Da der 
Timestamp aber im verlaufe des Scriptes noch genutzt wird (auslastung) 
ist das so schon ganz toll.

Nichtsdestotrotz werde ich mir dennoch die Trigger Möglichkeiten mal 
ansehen, das ich das auch mal im Kopf habe ☺️

von Philipp K. (philipp_k59)


Bewertung
0 lesenswert
nicht lesenswert
Schonmal das erste google ergebnis ausprobiert, hier zum Beispiel 5 
Minuten?

GROUP BY UNIX_TIMESTAMP(timestamp) DIV 300

Meine erste Idee war mit Modulo, aber da gab es hier wohl bessere 
vorschläge.

UNIX_TIMESTAMP(timestamp)-UNIX_TIMESTAMP(timestamp)%60

von Philipp K. (philipp_k59)


Bewertung
0 lesenswert
nicht lesenswert
ich musste das mal ausknobeln..

SELECT ip,time, 
FROM_UNIXTIME(unix_timestamp(time)-unix_timestamp(time)%300) as time2, 
COUNT(*) FROM `stopforumspam` WHERE 1 GROUP BY time2

wie schnell das ist kommt ja immer auf den Host an.

von Ben B. (Firma: Funkenflug Industries) (stromkraft)


Bewertung
0 lesenswert
nicht lesenswert
Was ich bei sowas mache, daß ich mir die Zeit in einer zweiten 
(indexierten) Spalte "passend ungenauer" gleich bei der Erstellung des 
Datensatzes speichere wenn ich weiß, daß ich später danach suchen werde 
oder eine passende Ausgabe brauche. Kostet ein wenig mehr Speicherplatz, 
aber bringt unglaublich viel Geschwindigkeit.

von Sheeva P. (sheevaplug)


Angehängte Dateien:

Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:
> Rene K. schrieb:
>> Das wird auch der Punkt sein, wo ich zukünftig anpacken muss. Da die
>> Daten über einen Cronjob mittels Script reinkommen, werde ich die Zeit
>> wohl nicht mehr automatisch eintragen lassen, sondern werde sie manuell
>> in die Tabelle einfügen lassen - da umgehe ich auch in Zukunft dieses
>> Problem. Die aktuelle Tabelle werde ich umschreiben lassen.
>
> Hab das gleich mal geändert. So kommen erst gar keine falsche Daten in
> die Tabelle:
>
>
> TimeStamp=$(python -c "from datetime import datetime; print 
> datetime.utcnow().strftime('%Y-%m-%d %H:%M:00.00')[:-4]")
> 
date +'%F %T'  # Ergebnis: "2020-02-03 13:35:38"
date +'%F %R'  # Ergebnis: "2020-02-03 13:35"

>
> sudo nmap -sP 192.168.178.0/24  | tr -d '()' | awk -vtimest="$TimeStamp" 
> '/Nmap scan report/{if($6=="") {$6=$5;$5="None"} print "INSERT INTO 
> hosts_2 (ip,host,time,mac) 
> VALUES(\""$6"\",\""$5"\",\""timest"\"";getline;getline;print",\""$3"\");";}' 
> | mysql ....
> 

Äh, entschuldige, aber warum machst Du denn solche Verrenkungen, wenn 
Dir doch Python zur Verfügung steht? Ich hab' da einmal spaßeshalber 
etwas mit Python gebaut und angehängt... HTH, HF! ;-)

von Frank M. (ukw) (Moderator) Benutzerseite


Bewertung
0 lesenswert
nicht lesenswert
Ben B. schrieb:
> Was ich bei sowas mache, daß ich mir die Zeit in einer zweiten
> (indexierten) Spalte "passend ungenauer" gleich bei der Erstellung des
> Datensatzes speichere wenn ich weiß, daß ich später danach suchen werde
> oder eine passende Ausgabe brauche.

Vielleicht kannst Du da mal näher erklären.

Nehmen wir mal an, ich schreibe die Timestamps mit Minutengranularität - 
inkl. Runden:
12:23:29 -> 12:23:00
12:23:30 -> 12:24:00
Die Timestamps liegen nur 1 Sekunde auseinander, das Resultat zeigt aber 
immer noch zwei verschiedene Timestamps.

Auch, wenn ich nicht runde, verschiebe ich das Problem nur:
12:23:59 -> 12:23:00
12:24:00 -> 12:24:00

von Rene K. (xdraconix)


Bewertung
1 lesenswert
nicht lesenswert
Sheeva P. schrieb:
> Äh, entschuldige, aber warum machst Du denn solche Verrenkungen, wenn
> Dir doch Python zur Verfügung steht? Ich hab' da einmal spaßeshalber
> etwas mit Python gebaut und angehängt... HTH, HF! ;-)

Oh vielen Dank, naja so ganz konform bin ich in Python noch nicht. Aber 
ich probiere das doch glatt mal aus. Vielen Dank für die Mühe ☺️☺️☺️

von Sheeva P. (sheevaplug)


Bewertung
0 lesenswert
nicht lesenswert
Rene K. schrieb:
> Oh vielen Dank, naja so ganz konform bin ich in Python noch nicht. Aber
> ich probiere das doch glatt mal aus. Vielen Dank für die Mühe ☺️☺️☺️

Sehr gerne, war aber gar keine Mühe. Wenn Du Fragen oder Anregungen 
hast, immer gerne! ;-)

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [avrasm]AVR-Assembler-Code[/avrasm]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.