Sortere etter dato i en "varchar" celle MySQL?

[bazzo]

Hvordan kan jeg sortere etter en dato som er postet i en varchar celle i en MySQL database.

 

Datoenene ser slik ut i db.

13.03.2006 - 16:11:48

 

Kan strippe bort tiden, er kun dato som er viktig her.

Endret 30. april 2006 av bazzo

[genstian]

SELECT * FROM tabel ORDER BY dato ?

[????????]

Dette vil være håpløst mye jobb for MySQL så lenge du ikke gjør om feltet til dato. Måten til hotstian vil neppe fungere. Du kan løse det med UNIX_TIMESTAMP().

 

SELECT felt1, felt2 FROM tabell ORDER BY UNIX_TIMESTAMP(datofelt). Som sagt vil dette være MYE jobb for SQL serveren din, og jeg vil anbefale deg å endre felttypen!

[gnab]

Du kan enkelt hente ut de enkelte komponentene av datoen og sortere etter disse slik:

 

select *, substr(dato, 1, 2) as dag, substr(dato, 4, 2) as maaned, substr(dato, 7, 4) as aar from tabell order by aar, maaned, dag;

 

Men som nevnt, bruk den felttypen som er tiltenkt den typen du vil lagre, litt bom med varchar for lagring av dato/klokkeslett.

[????????]

Kjør en EXPLAIN på den gnab... vet ikke helt om jeg ville anbefale den.

[bazzo]

Takker for svar, er det en enkel måte å konvertere feltet til date uten å fucke databasen?

Denne cellen var egentlig ikke tenk til å sorteres når jeg lagde den så jeg satte den ikke til date.

 

Håper det finnes en enkel måte å fikse dette på, ligger masse data i den dben nå.

Endret 30. april 2006 av bazzo

[gnab]

Det som skjer er ganske enkelt at du henter ut dag, måned og år fra dato-feltet vha. substr-metoden, substr(A, B, C), som klipper ut C tegn, fra og med tegn B i kolonnen A. Deretter sorteres det etter år, måned og til slutt dag.

 

mysql> select *, substr(dato, 1, 2) as dag, substr(dato, 4, 2) as maaned, substr(dato, 7, 4) as aar from test order by aar, maaned, dag;
+------------+------+--------+------+
| dato       | dag  | maaned | aar  |
+------------+------+--------+------+
| 07.02.2006 | 07   | 02     | 2006 |
| 13.03.2006 | 13   | 03     | 2006 |
| 12.10.2006 | 12   | 10     | 2006 |
| 12.11.2006 | 12   | 11     | 2006 |
| 12.20.2006 | 12   | 20     | 2006 |
+------------+------+--------+------+

 

Når du snakker om å anbefale eller ikke så vil jeg ikke anbefale den, men den løser problemet trådstarter kom med. Er som nevnt ikke den beste løsningen, men viser at det er mulig.

Endret 30. april 2006 av gnab

[????????]

gnab, jeg mente ikke forklar hva som skjer. Å kjøre en EXPLAIN betyr at du kan kjøre kommandoen EXPLAIN SPØRRING så får du se hvordan f.eks. MySQL vil behandle spørringen. Den gir deg mulighet til å mye enklere kunne optimalisere en spørring. Videre så må jeg si at jeg ikke helt ser poenget med å foreslå en løsning du ikke vil anbefale.

 

bazzo: ta en kopi av databasen så prøver du rett og slett å konvertere feltet.

[gnab]

Mtp. å bytte felttype tror jeg det letteste vil være å opprette en ny kolonne og flytte over fra den gamle til den nye før du til slutt sletter den gamle.

 

Dersom datoformatet ditt er NØYAKTIG "dd.mm.åååå - hh:m:ss" (slik du oppgav innledningsvis), kolonnen heter "dato" og tabellen "tabell" kan du bruke følgende kommandoer:

 

alter table tabell add column nyDato DATETIME;

update tabell set nyDato = concat(substr(dato, 7, 4), "-", substr(dato, 4, 2), "-", substr(dato, 1, 2), " ", substr(dato, 14, 2), ":", substr(dato, 17, 2), ":", substr(dato, 20, 2));

alter table tabell drop column dato;

alter table tabell change nyDato dato DATETIME;

 

Det opprettes da en ny kolonne med korrekt felttype, DATETIME, og deretter kopieres alle verdier over fra "dd.mm.åååå - hh:mm:ss" i den gamle kolonnen til "åååå-mm-dd hh:mm:ss" i den nye kolonnen. Til slutt slettes den gamle kolonnen og den nye gis navnet til den gamle/opprinnelige kolonnen.

 

Edit: Når jeg tenker meg om vil det kanskje duge å kjøre UPDATE-spørringen på den gamle kolonnen og deretter endre felttype, altså ikke gå veien om en annen kolonne. Det burde gå siden kolonnen før konvertering da er riktig formatert. Men før det evt. prøves er kopi av database allerede nevnt.

Endret 30. april 2006 av gnab

[gnab]

??????, har aldri vært borti EXPLAIN før, men den er notert. Den første løsningen min var en løsning på trådstarters problemstilling; han spurte om den kunne sorteres, og det kan den. Men jeg er selvfølgelig enig med deg i at det ikke er optimalt, slik jeg har poengtert flere ganger.

 

Forøvig fungerer det ikke å konvertere feltet ved bare å bytte til DATETIME i hvert fall.

Endret 30. april 2006 av gnab

[genstian]

Hvorfor har du den i varchar format?

[bazzo]

Hvorfor har du den i varchar format?

6018222[/snapback]

Denne cellen var egentlig ikke tenk til å sorteres når jeg lagde den så jeg satte den ikke til date.

[roac]

Denne cellen var egentlig ikke tenk til å sorteres når jeg lagde den så jeg satte den ikke til date.

6020070[/snapback]

Da håper i hvert fall jeg at du ikke gjør denne blunderen en gang til. "Bad design since I do not need good design" er ingen god idé, som du merket

 

Forøvrig vil jeg påpeke at det ikke nødvendigvis "bare" er å konvertere en kolonne, for som regel har du en eller annen applikasjon eller noen script som jobber mot denne kolonnen, og de må muligens også skrives om, så dette kan være alt fra trivielt til ganske mye jobb.

 

Uansett, lykke til videre

[bazzo]

ja, treger meg ihjel for at jeg ikke satte den til date i første omgang. Er litt ny enda på dette feltet, men man lærer av slike feil.

 

Må se litt hvordan jeg skal gjøre dette, finner nok en løsning på det.

 

Takker for all hjelp så langt.

[bazzo]

Mtp. å bytte felttype tror jeg det letteste vil være å opprette en ny kolonne og flytte over fra den gamle til den nye før du til slutt sletter den gamle.

 

Dersom datoformatet ditt er NØYAKTIG "dd.mm.åååå - hh:m:ss" (slik du oppgav innledningsvis), kolonnen heter "dato" og tabellen "tabell" kan du bruke følgende kommandoer:

 

alter table tabell add column nyDato DATETIME;

update tabell set nyDato = concat(substr(dato, 7, 4), "-", substr(dato, 4, 2), "-", substr(dato, 1, 2), " ", substr(dato, 14, 2), ":", substr(dato, 17, 2), ":", substr(dato, 20, 2));

alter table tabell drop column dato;

alter table tabell change nyDato dato DATETIME;

 

Det opprettes da en ny kolonne med korrekt felttype, DATETIME, og deretter kopieres alle verdier over fra "dd.mm.åååå - hh:mm:ss" i den gamle kolonnen til "åååå-mm-dd hh:mm:ss" i den nye kolonnen. Til slutt slettes den gamle kolonnen og den nye gis navnet til den gamle/opprinnelige kolonnen.

 

Edit: Når jeg tenker meg om vil det kanskje duge å kjøre UPDATE-spørringen på den gamle kolonnen og deretter endre felttype, altså ikke gå veien om en annen kolonne. Det burde gå siden kolonnen før konvertering da er riktig formatert. Men før det evt. prøves er kopi av database allerede nevnt.

6017947[/snapback]

 

Nå gjorde jeg slik du sa men fikk problemer med "update" scriptet.

 

Fikk denne meldingen i phpMyAdmin

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(time, 7, 4), "-", substr(time, 4, 2), "-", substr(time, 1, 2),

[gnab]

You will get a MySQL query syntax error number 1064 when you incorrectly use a reserved word in your query such as "when" or "order".

 

Tipper det skyldes ordet TIME, som kolonnen din heter; bruk heller tabell.kolonne i stedet for bare kolonne i spørringen.

Endret 2. mai 2006 av gnab

[bazzo]

Tipper det skyldes ordet TIME, som kolonnen din heter; bruk heller tabell.kolonne i stedet for bare kolonne i spørringen.

6031371[/snapback]

 

Hmm testet det nå, fikk samme meldingen. Prøvde også å endre time kolonnen min til "time2" men fikk samme melding da også.

 

Oppsummering:

Jeg har en tabell som heter "orders" med en kolonne som heter "time" som jeg var så dum å sette som "varchar". I kolonnen ligger det en dato og tid "13.03.2006 - 16:11:48".

 

Jeg opprettet en ny kolonne med "datetime" i samme tabell som heter "ordered".

 

update orders set ordered = concat(substr(time2, 7, 4), "-", substr(time2, 4, 2), "-", substr(time2, 1, 2), " ", substr(time2, 14, 2), ":", substr(time2, 17, 2), ":", substr(time2, 20, 2));

Burde ikke da denne fungert?

Endret 3. mai 2006 av bazzo

[bazzo]

Da fikk jeg det endelig til, jeg lagde et script i en php fil som fikset det hele

 

$result = $myDB->queryDB("SELECT * FROM tabell");

while ($row = mysql_fetch_object($result)) {
 $id = $row->id;
 
 $d = substr($row->time,0,2);
 $m = substr($row->time,3,2);
 $Y = substr($row->time,6,4);
 $H = substr($row->time,13,2);
 $i = substr($row->time,16,2);
 $s = substr($row->time,19,2);
 
 $xdato = $Y ."-". $m ."-". $d ." ". $H .":". $i .":". $s;
 
 $query = $myDB->queryDB("UPDATE tabell SET time2='$xdato' WHERE id='$id'");
}

Var ikke verre en det egentlig

Var vel bare jeg som var litt nervøs for å ødelegge noe, tenkte kanskje litt for tungvindt til å begynne med.

 

Takker for alle svar

Endret 4. mai 2006 av bazzo