update varchar date to datetime

[Forumsgast]

Hallo,

ich suche schon 1,5 Tagen, finde aber leider keine Lösung. Aus einer .csv wird mir ein Datum mit falschem Format in die Datenbank geschrieben. In ein DateTime Feld wird nichts übernommen, daher habe ich das Datum zunächst in ein Varchar Feld importiert, möchte dieses dort richtig formatieren und dann in mein DateTime Feld kopieren. So richtig?

Das Format: "2012/10/03 4:55 am"
kann aber auch so aussehen: "2012/10/03 12:55 am"

Nun fehlt mir wohl die UPDATE funktion für MySQL...dankbar für einen kurzen Geistesblitz...

Angenommen folgendes Datum wird importiert: "20412/-10/03 4:55 NotValid", dann würde ich dieses nicht umbauen wollen, um ggf. Fehler beim importieren von falschen Daten zu sehen.

[Forumsgast]

So richtig?

Viele Wege führen nach Rom. Ist durchaus eine Möglichkeit. Zum umformatieren findest du hier die benötigten Funktionen: http://dev.mysql.com/doc/refman/5.1/...functions.html

[Forumsgast]

Danke...am liebsten würde ich das neue Format direkt ins DateTime Feld übernehmen und das TMP Datum so belassen...die Suche geht weiter

[Forumsgast]

...

*edit* falsches Fenster

[Forumsgast]

Danke...am liebsten würde ich das neue Format direkt ins DateTime Feld übernehmen und das TMP Datum so belassen...die Suche geht weiter

alter table ... alter column ... type ... using (...)

test=# create table blabla (t text);
CREATE TABLE
Time: 2,664 ms
test=*# insert into blabla values ('2012/10/10');
INSERT 0 1
Time: 0,323 ms
test=*# alter table blabla alter column t type date using(to_date(t,'yyyy/mm/dd'));
ALTER TABLE
Time: 4,670 ms
test=*# \d blabla
   Table "public.blabla"
 Column | Type | Modifiers
--------+------+-----------
 t      | date |

Andreas

[hts]

Einen Datumswert kannst du mit der Funktion STR_TO_DATE erstellen.

Um nur die gültigen Werte zu übernehmen müsstest du noch im WHERE einen REGEXP benutzen.

[Forumsgast]

Einen Datumswert kannst du mit der Funktion STR_TO_DATE erstellen.

Um nur die gültigen Werte zu übernehmen müsstest du noch im WHERE einen REGEXP benutzen.

Zeig mal ein regex, der z.B. auch einen falschen Schalttag erkennt

Andreas

[hts]

Zeig mal ein regex, der z.B. auch einen falschen Schalttag erkennt

Schade, ich habe auf die schnelle keinen gefunden - sieht bestimmt gut aus
[EDIT]
Hier ist einer: http://www.inspire-world.de/readarti...p?aid=28&los=5
Habe ich aber nicht getestet Aber ich glaube auch, der MySQL-Regex ist nicht so mächtig.

Ich meinte halt, dass man solche Gurken "20412/-10/03 4:55 NotValid" rausschmeißt.

Wenns auch um Schaltjahr- und andere Plausibilitätsprüfungen geht, würde ich ein PHP-Script für den Import vorziehen - das ist ja schnell geschrieben.

[Forumsgast]

Ich hab auch schon mal einen geschrieben.

[Forumsgast]

So richtig grün werde ich leider nicht. Nehme gerne eure Hilfe an, da ich auch mit Netz-Recherche nicht weiter komme

UPDATE DatimeCol
FROM table
SET DatetimeCol = SELECT STR_TO_DATE(DateImportedCol,'%Y-%m-%d %h:%i')
WHERE id = 1; 

Leider funktioniert schon die STR_TO_DATE Anweisung nicht.

Erlärtes Ziel:
Das durch die csv importierte Datum (kommt als VARCHAR in die Datenbank) direkt richtig formatiert in eine weitere Spalte mit FieldType DateTime überführen. Format kann sein:

2012/12/01 1:15 am
2012/12/01 10:15 pm

Gruss...

[Forumsgast]

So richtig grün werde ich leider nicht. Nehme gerne eure Hilfe an, da ich auch mit Netz-Recherche nicht weiter komme

UPDATE DatimeCol
FROM table
SET DatetimeCol = SELECT STR_TO_DATE(DateImportedCol,'%Y-%m-%d %h:%i')
WHERE id = 1; 

Leider funktioniert schon die STR_TO_DATE Anweisung nicht.

Erlärtes Ziel:
Das durch die csv importierte Datum (kommt als VARCHAR in die Datenbank) direkt richtig formatiert in eine weitere Spalte mit FieldType DateTime überführen. Format kann sein:

2012/12/01 1:15 am
2012/12/01 10:15 pm

Gruss...

In eine weitere Spalte? Das ist redundant, damit 5, setzen.

test=# create table knuffiwuffi(a text);
CREATE TABLE
Time: 8,600 ms
test=*# insert into knuffiwuffi values ('2012/12/01 1:15 am');
INSERT 0 1
Time: 0,311 ms
test=*# alter table knuffiwuffi alter column a type timestamp using(to_timestamp(a,'yyyy/mm/dd hh:mi am'));
ALTER TABLE
Time: 44,447 ms
test=*# \d knuffiwuffi
            Table "public.knuffiwuffi"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 a      | timestamp without time zone |

test=*# select * from knuffiwuffi ;
          a
---------------------
 2012-12-01 01:15:00
(1 row)

Time: 0,271 ms

Andreas

[Forumsgast]

Den Murks kannst du direkt beim Import machen: http://stackoverflow.com/a/11573541

[hts]

Format kann sein:
2012/12/01 1:15 am
2012/12/01 10:15 pm

SELECT STR_TO_DATE('2012/12/01 1:15 am', '%Y/%m/%d %h:%i %p');
SELECT STR_TO_DATE('2012/12/01 10:15 pm', '%Y/%m/%d %h:%i %p')

2012-12-01 01:15:00
2012-12-01 22:15:00

[Forumsgast]

@all

erstmal vielen lieben Dank...es funktioniert!

Der Import kann gerne mal 500K Datensätze betragen, daher werden alle Änderungen in einem TMP table erledigt und danach wird verschoben. Ich denke damit dürfte MySQL schneller arbeiten.

$convert_date = "UPDATE tmp_table
SET col_Date = STR_TO_DATE(col_tmp_Date,'%Y/%m/%d %h:%i %p')
WHERE id = " . $csv_id;

sc_exec_sql($convert_date); 

Closed

[Forumsgast]

500k is nix Frag nach performance wenn deine Datensätze sich verhundertfacht haben. Zumindest wirst du da nix groß merken wenn du beim Import konvertierst. Schließlich betrifft das nur ein Feld und bleibt im selben Table.