[SQL] Timestamp auf volle Minuten runden / kürzen

[Rene K. (xdraconix)]

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.

[imonbln (Gast)]

was ist mit der extract Funktion, die sollte machen was du willst.

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

[T.roll (Gast)]

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

[Rene K. (xdraconix)]

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.

[T.roll (Gast)]

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.

[Egon D. (Gast)]

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...

[c-hater (Gast)]

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]?

[Rene K. (xdraconix)]

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.

[Rene K. (xdraconix)]

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 ....

[DB (Gast)]

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.

[Rene K. (xdraconix)]

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 ☺️

[Philipp K. (philipp_k59)]

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

[Philipp K. (philipp_k59)]

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.

[Ben B. (Firma: Funkenflug Industries) (stromkraft)]

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.

[Sheeva P. (sheevaplug)]

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! ;-)

[Frank M. (ukw) (Moderator)]

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

[Rene K. (xdraconix)]

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 ☺️☺️☺️

[Sheeva P. (sheevaplug)]

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! ;-)