Datumsangaben in Unix-Timestamp konvertieren (MySQL-Problem)

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Caldar
User
Beiträge: 46
Registriert: Sonntag 17. Mai 2009, 18:20

Ich habe ein Python-Skript, das mir eine csv-Datei mittels LOAD DATA INFILE in eine Tabelle namens temp schreibt.
Soweit so gut, ich habe in der Tabelle nun u.a. eine Spalte Datum, welche Datumsangaben in folgender Darstellung enthält:

23.04.2009 15:30:12
Das Datum-Feld ist VARCHAR(50), heißt Datum und wurde automatisch vom LOAD DATA INFILE-Befehl so angelegt

Ich möchte nun für jeden Datensatz das jeweilige Datum in einen UNIX-Timestamp konvertieren und in einem neuen Feld namens Timestamp speichern.
Dazu habe ich folgende MySQL-Syntax bemüht:
UPDATE temp SET `Timestamp` = UNIX_TIMESTAMP(DATE_FORMAT(`Datum`,'%d.%m.%Y %H:%i:%s')) WHERE `Datum` IS NOT NULL
Das Feld Timestamp wurde als INT angelegt.

Das Problem ist: es funktioniert nicht: Ich bekomme für jeden Datensatz den Fehler:
Warning: Incorrect datetime value: '02.01.2007 18:20:00'(als Bsp.)
Benutzeravatar
Hyperion
Moderator
Beiträge: 7478
Registriert: Freitag 4. August 2006, 14:56
Wohnort: Hamburg
Kontaktdaten:

Also der Name Timestamp für ein Attribut ist wenig glücklich, ist es doch ein MySQL-Datentyp (auch wenn sich MySQL daran wohl nicht stört).

Lass Dir doch mal einfach per select die Daten so anzeigen nach Deiner Formatierung. Ich wette der Fehler tritt auch dabei auf. Kürze Deinen Dateformat-String auf ein Minimum und erweitere ihn Stück für Stück mit den bisherigen Angaben, bis der Fehler auftritt. Dann wirst Du erkennen, an welchem Format-Parameter es hapert.

Generell würde ich aber das Vorgehen ändern, also das Datum vor dem Importieren in MySQl so abändern, dass es in einen timestamp-Typen passt!
Caldar
User
Beiträge: 46
Registriert: Sonntag 17. Mai 2009, 18:20

Also das Problem ist, wie ich gerade gelesen habe, dass UNIX_TIMESTAMP den Typ Timestamp erwartet, aber mein Datum ja in einer VARCHAR-Spalte gespeichert ist.
D.h. ich müsste, wie Du sagst, idealerweise den Datumswert in der csv-Datei schon in einen Unix-Timestamp beim Importieren konvertieren, also bei der LOAD DATA INFILE-Anweisung.
Diese sieht bei mir so aus im Moment:

LOAD DATA INFILE 'D:\\file.csv' INTO TABLE abc.temp
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

Ich vermute, dass ich mit einer SET-Anweisung hier etwas bezüglich der Konvertierung meines Datumsformates in einen Unix-Timestamp beim EInlesen der Datei was machen könnte. Aber mit der MySQL-Doku komme ich dabei nicht so ganz klar.
Benutzeravatar
Hyperion
Moderator
Beiträge: 7478
Registriert: Freitag 4. August 2006, 14:56
Wohnort: Hamburg
Kontaktdaten:

Dann versuche es doch Python seitig. Schreib ein kleines Script, das die Datei per csv-Modul einliest und diese eine Spalte umwandelt und dann zurückschreibt. Sollte doch recht schnell erledigt sein.
Caldar
User
Beiträge: 46
Registriert: Sonntag 17. Mai 2009, 18:20

Habs direkt per MySQL-Befehl lösen können:


Im Vorfeld habe ich eine neue Spalte vom Typ Datetime angelegt.
Diese habe ich befüllt mit dem Befehl:

UPDATE temp SET `Datum_neu` = STR_TO_DATE(`Datum`,'%d.%c.%Y %H:%i:%s')

Nun muss ich nur noch diesen Wert in einen Unix-Timestamp umwandeln.
Ich meld mich, wenn ich soweit bin.
Vielleicht kann ich sogar alles in einen Befehl packen?

EDIT:
Alles in einem siehts so aus:

Eine Spalte Unixtime (INT) angelegt
Dann forlgenden Befehl:

UPDATE temp SET `Unixtime` = UNIX_TIMESTAMP(STR_TO_DATE(`Datum`,'%d.%c.%Y %H:%i:%s'))

Wenn ich das jetzt noch in den LOAD DATA INFILE-Befehl bringe, bin ich noch glücklicher :)
hugo20
User
Beiträge: 1
Registriert: Montag 18. Juli 2011, 11:31

Caldar hat geschrieben:Vielleicht kann ich sogar alles in einen Befehl packen?
Und, ist das zwischenzeitlich gelungen? :K Oder anders gefragt: Bist Du schon glücklich? :wink:

Ich hab gerade eben diesen Thread entdeckt, weil ich auf der Suche nach einer Lösung für einen ganz ähnlich gelagerten Fall bin.

Mein Problem ist, das in meinem Eingabefeld die Werte so drinstehen :arrow: ;30-05-2011 01:08;
Also DD-MM-YYYY HH:MM

Dabei fehlen bis zum "Timestamp" wohl offensichtlich noch, dass bei der Zeit die SS, also Sekunden dazu und das Datum komplett umgedreht werden muss.
Also auf YYYY-MM-DD.
Das Ganze, so hatte ich mir dat zumindest vorgestellt, eben am Besten direkt im "Load Data" unter MySQL.(Kein Bock auf ein Script, zumal ich mich da dann auch wieder verkopfen müsste, weil ne dynamische, vorstehende lfd. Nummer den ganzen String immer verschiebt.)

Hat da zufällig jemand ne "elegante" Syntax parat? :K
Achja, sämtliche Felder im Eingabe-Datensatz sind Delimited By ;
Antworten