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

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:

1

(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 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:

1

SELECT DATE_FORMAT(`time`, '%d.%m.%Y %H:%i'), COUNT(*) 

2

FROM hosts 

3

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

1

TimeStamp=$(python -c "from datetime import datetime; print datetime.utcnow().strftime('%Y-%m-%d %H:%M:00.00')[:-4]")

2

3

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

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

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.

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.

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

1

> TimeStamp=$(python -c "from datetime import datetime; print 

2

> datetime.utcnow().strftime('%Y-%m-%d %H:%M:00.00')[:-4]")

3

>

1

date +'%F %T'  # Ergebnis: "2020-02-03 13:35:38"

2

date +'%F %R'  # Ergebnis: "2020-02-03 13:35"

>

1

> sudo nmap -sP 192.168.178.0/24  | tr -d '()' | awk -vtimest="$TimeStamp" 

2

> '/Nmap scan report/{if($6=="") {$6=$5;$5="None"} print "INSERT INTO 

3

> hosts_2 (ip,host,time,mac) 

4

> VALUES(\""$6"\",\""$5"\",\""timest"\"";getline;getline;print",\""$3"\");";}' 

5

> | mysql ....

6

>

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

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:

1

12:23:29 -> 12:23:00

2

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:

1

12:23:59 -> 12:23:00

2

12:24:00 -> 12:24:00

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

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