year - postgresql string to timestamp

Cómo convertir cadenas de fecha en timestamp sin conocer el formato de fecha

Question

Estoy intentando escribir una consulta para insertar un valor en una timestamp with no timezone data campo de tipo de timestamp with no timezone data . El valor proviene del archivo CSV.

La versión con la que estoy trabajando es PostgreSQL 8.1.21 .

La carga del archivo CSV es realizada por el cliente y tiene una columna de fecha. La fecha a veces viene como formatos ''28-Sep-13'' y algunas veces como ''28/09/2013'' .

Traté de usar lo siguiente para convertir la cadena en timestamp: str_date::timestamp .

Esto funciona bien si str_date es algo así como ''28-Sep-13'' pero no funcionará si la fecha de entrada tiene el formato ''28/09/2013'' , cuando se produce este error:

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

Básicamente, el cliente sigue cambiando el formato de fecha en el archivo CSV cargado.
¿Hay alguna manera de convertir las cadenas de fecha en timestamp dependiendo de su formato real?

Answer 1

Debes configurar tu datestyle a "ISO, DMY". Se establece en "ISO, MDY" de forma predeterminada y provocaría un error en su ejemplo:

> 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)

(ejemplos hechos en PostgreSQL 9.1, pero la configuración DateStyle y el comportamiento asociado son antiguos, por lo que debería funcionar bien)

Answer 2

Estoy de acuerdo con Erwin, pero trataría de crear la función de base de datos (PL / pgSQL, PL / Python u otro idioma) que pueda convertir varias cadenas de date en date . En la respuesta de Erwins puedes ver WHEN ... THEN y puedes usarlo. Tal función será más fácil de probar y mantener.

Answer 3

Puede eludir el problema con estos pasos:

1. Cree una tabla temporal vacía con la misma estructura que la tabla de destino:

   CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;

2. Cambiar el tipo de la columna problemática a texto :

   ALTER TABLE tmp ALTER COLUMN str_date TYPE text;

3. Importar datos a la tabla temporal. Debería funcionar bien ahora:

   COPY tmp FROM ''/path/to/my/file.txt'';

4. INSERT en la tabla de destino dependiendo de dependiendo del contenido real de la columna:

   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