Datumsformate

Hallo,

dummerweise habe ich eine „historische DB“ mit einer Spalte „Datum“ geerbt. Da bisher Freitext-Eingaben für das Datum möglich waren, sind natürlich alle möglichen Schandtaten enthalten. Immerhin handelt es sich grundsätzlich immer um 8 Ziffern ohne Trennzeichen, die ich mittlerweile in einer psql-Db per Textformat integrieren konnte. O.k., das mag ich auch nicht, aber ich baue halt keine DB auf, um dann Daten einzugeben, sondern versuche, vorhandene Daten in eine vernünftige psql-DB zu überführen und handhabbar zu machen.
Folgende Beispiele sind charakteristisch (Text bzw. CLOB ):
12122002
00122003
00002004

Für meine Zwecke ist es tatsächlich sinnvoll, folgende Daten auch später abfragen zu können:


  1. Das konkrete Datum z.B. 12.12.2012
  2. Den konkreten Monat in einem konkreten Jahr z.B. 00.12.2003 besser „im Dezember 2003“
  3. Das konkrete Jahr weder nach Monat oder Tag differenziert z.B. 00.00.2004 besser „irgendwann im Jahr 2004“

Welche Ideen -außer „schmeiß alles ohne Tag und Monat raus“ oder „fang von vorn an“ - wären aus eurer Sicht sinnvoll? Ich habe 64.000 Datensätze und bin gegen händisches Ändern. :wink:


Grüße
C.

Die Frage für mich ist erst einmal, ob die ‘ungenauigkeit’ der bisherigen Daten Träger einer Information ist. Du könntest ja, z.B. aus ^00 ein ^01 machen oder wenn sunstring(string,3,2) == ‘00’ ist dann ein ‘01’ draus machen - auf dem Wege könntest Du sehr trivial aus allen Werten gültige Daten machen - die dann aber eine Genauigkeit vortäuschen, die sie nicht haben. Du kannst dann nicht mehr am ‘00’ für Tag oder Monat sehen, wie ‘genau’ das ursprünglich war.

Du könntest natürlich auch da, wo der Tag oder der Monat fehlt, in einer extra Spalte dies Dir merken und später bei Abfragen mit auswerten.

test=# select * from alfredo;
    d
----------
 12122002
 00122003
 00002004
(3 rows)

Time: 0,217 ms
test=*# alter table alfredo alter column d type date using to_date(case when substring(d,1,2) = '00' then '01' else substring(d,1,2) end || case when substring(d,3,2)='00' then '01' else substring(d,3,2) end || substring(d,5,4),'ddmmyyyy');
ALTER TABLE
Time: 5,960 ms
test=*# select * from alfredo;
     d
------------
 2002-12-12
 2003-12-01
 2004-01-01
(3 rows)

Mal so als frühe Fingerübung :wink:


Andreas

Eine tolle “Fingerübung”! Ich wünschte, ich hätte auch so wissende flinke Finger. :wink: Vielen Dank. Die Idee passt für mich 100%ig. Mittlerweile habe ich einen Großteil begriffen, nur "type date using to_date (…, ‘ddmmyyy’) fehlt mir noch…
Da mit den vollständigen Datumswerten auch perspektivisch Statistik gemacht werden soll, benötige ich tatsächlich eine neue Spalte e, in der die Genauigkeit (tages-, monats-, jahresganau) verzeichnet ist. Ungefähr so:


1.
d

12122002
00122003
00002004

d e

12122002 day
00122003 month
00002004 year

d e

2002-12-12 day
2003-12-01 month
2004-01-01 year

Die Spalte müsste man wohl zuerst erstellen und füllen. Kann man „substring“ mit case if und UPDATE dafür kombinieren? Ist das die richtige Spur? Von string-Funktionen habe ich bisher keine Ahnung und muss mich damit beschäftigen.

Carsten

Jepp, so in etwa, falls ich grad nicht völlig daneben bin, dann so:

test=*# alter table alfredo add column e text;
ALTER TABLE
Time: 0,336 ms
test=*# update alfredo  set e = 'tag' where substring(d,1,2) = '00';
UPDATE 2
Time: 0,311 ms
test=*# update alfredo  set e = 'monat' where substring(d,3,2) = '00';
UPDATE 1
Time: 0,286 ms
test=*# select * from alfredo;
    d     |   e
----------+-------
 12122002 |
 00122003 | tag
 00002004 | monat
(3 rows)

Andreas

Nun ja, Syntax-Zucker oder so… man kann den String nicht so ohne weiteres als DATE interpretieren, daher gibt man eine Funktion an, die das übernimmt. PG kann ja nicht alles erraten. So z.B. nicht, daß aus einem ‘00’ ein ‘01’ werden soll.

Andreas

Vielen Dank!
:smiley:

Warum bin ich Hirn gestern nicht eigentlich gleich auf die vieeeel elegantere Lösung mit DATERANGE gekommen?

Ich hab da mal was vorbereitet …

test=# select * from alfredo;
         bereich
-------------------------
 [2012-12-31,2013-01-01)
 [2013-01-01,2014-01-01)
 [2013-01-01,2013-02-01)
(3 rows)

Time: 0,273 ms
test=*# select * from alfredo where bereich @> current_date;
         bereich
-------------------------
 [2012-12-31,2013-01-01)
(1 row)

Time: 13,524 ms
test=*# select * from alfredo where bereich @> '2013-01-05'::date;
         bereich
-------------------------
 [2013-01-01,2014-01-01)
 [2013-01-01,2013-02-01)
(2 rows)

Deine Daten in das DATERANGE-Format zu bekommen überlasse ich Dir mal zur Übung. Notfalls kann ich Dir auch kurzfristig helfen - im nächsten Jahr. Der Vorteil wäre halt, daß ALLE Informationen in EINER Spalte stehen, das sehr elegant abzufragen ist und auch Indexe nutzbar sind.


Andreas

Kurz vor dem Jahresende schaue ich noch einmal hinein und… Du gibst mir wirklich viel zu üben und lernen! Bis vor deinem letzten Post habe ich das meiste nachvollziehen kïönnen. Dies sehe ich mir im neuen Jahr an. Nochmals herzlichen Dank und Prosit! :sunglasses:

Carsten

Hi Andreas,

das mit dem DATERANGE hat sehr sympathische Seiten. Ich möchte später auch Zeitabstände auswerten, vielleicht ist es aus dem Grunde auch besser, mit DATERANGE zu arbeiten.
Einen kleinen Pferdefuss gibt es allerdings noch: ich benutze z.Zt. noch 8.4 und noch nicht 9.x.

Carsten

DATERANGE gibt es ab 9.2. Was hindert Dich am Update?

Andreas

Ich weiß, es ist kindisch, abver ich wollte erst von XP auf Win7 und danach auf PostgreSQL 9.x updaten. Das braucht wohl noch 1-2 Wochen.

Vielen Dank!

ich habe zwar die Antworten von Andreas gelesen aber ich frage mich ob du die Daten wirklich “so” übernehmen willst.
das “Datum” Format DDMMYYYY würde “sortiert” so was ergeben

12122002
12122003
12122004
12122005
13122002
14122002
15122002

… ist das “so” gewollt ?

ich würde mir überlegen das DDMMYYYY Format nach YYYYMMDD zu ändern damit die “Sortierung” stimmt.

Danke für den Hinweis. Das Gehampel mit dem String zerlegen da ist unsinnig, es gibt to_date :wink:

Andreas

Deine Anmerkung ist richtig falls die Daten als Text gespeichert werden.

In diesem Thread ging es aber darum, die Text-Daten in einem echten Datumsfeld zu speichern. Dann ist die Sortierung von der Darstellung unabhängig.