Come convertire le stringhe di data timestamp senza sapere il formato della data

Sto cercando di scrivere una query per inserire un valore in una timestamp with no timezone data tipo di campo. Il valore è venuta da file CSV.

La versione a cui sto lavorando è PostgreSQL 8.1.21.

CSV file upload è a cura del cliente e si ha una data colonna. Data a volte si presenta come '28-Sep-13' e, a volte, come '28/09/2013' formati.

Ho provato a usare la seguente procedura per lanciare la stringa in timestamp:
str_date::timestamp.

Questo funziona bene se str_date è qualcosa di simile a '28-Sep-13' ma non funziona se l’arrivo di una data è nel formato '28/09/2013', quando si verifica questo errore:

ERROR: date/time field value out of range: "28/09/2013"  
HINT:  Perhaps you need a different "datestyle" setting

Fondamentalmente il client continua a cambiare il formato della data in upload del file CSV.
C’è un modo per convertire le stringhe di data in timestamp a seconda delle proprie effettive formato?

InformationsquelleAutor Shiver | 2011-11-15

 

3 Replies
  1. 15

    È necessario impostare il vostro datestyle a “ISO, DMY”. È impostato su “ISO, MDY” per impostazione predefinita, e potrebbe causare il vostro esempio, a fallire:

    > show datestyle;
    
     DateStyle 
    -----------
     ISO, MDY
    (1 row)
    
    > select '28-Sep-13'::date;
        date    
    ------------
     2013-09-28
    (1 row)
    
    > select '28/09/2013'::date;
    ERROR:  date/time field value out of range: "28/09/2013"
    LINE 1: select '28/09/2013'::date;
                   ^
    HINT:  Perhaps you need a different "datestyle" setting.
    
    > set datestyle = 'ISO, DMY';
    SET
    
    > select '28-Sep-13'::date;
        date    
    ------------
     2013-09-28
    (1 row)
    
    > select '28/09/2013'::date;
        date    
    ------------
     2013-09-28
    (1 row)

    (esempi fatto in PostgreSQL 9.1, ma il DateStyle impostazione e il comportamento associato sono antiche, così dovrebbe funzionare bene)

    • Grazie Matteo per l’aiuto. Ho aggiunto datestyle = ‘ISO, DMY’ per la query ed il problema è risolto.
    • Felice di sentire che è stato risolto il problema. Se si potrebbe essere così gentile da segnalare la risposta accettata, sarebbe apprezzato!
  2. 7

    È possibile aggirare il problema con questi passaggi:

    1. Creare un vuoto tabella temporanea con la stessa struttura della tabella di destinazione:

      CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;
    2. Modificare il tipo della colonna problematico per testo:

      ALTER TABLE tmp ALTER COLUMN str_date TYPE text;
    3. Importazione dati per la tabella temporanea. Dovrebbe funzionare bene adesso:

      COPY tmp FROM '/path/to/my/file.txt';
    4. INSERT nella tabella di destinazione a seconda a seconda del contenuto effettivo della colonna:

      INSERT INTO real_tbl (col1, col2, col3, date_col)
      SELECT col1, col2, col3
           , CASE WHEN str_date ~~ '%/%'
                THEN to_date(str_date, 'DD/MM/YYYY')
             WHEN str_date ~~ '%-%'
                THEN to_date(str_date, 'DD-Mon-YYYY')
              -- more cases?
             ELSE ???
             END AS date_col
      FROM   tmp;
      
      -- DROP TABLE tmp;  -- optional; dropped at end of session automatically
  3. 1

    Sono d’accordo con Erwin, ma vorrei provare a creare il database di funzione (PL/pgSQL, PL/Python o altra lingua) che può convertire le varie stringhe di data in date. In Erwins risposta si può vedere WHEN ... THEN e si può utilizzare. Tale funzione sarà più facile di testare e mantenere.

Lascia un commento