How to convert date strings to timestamp without knowing the date format

Question

I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file.

The version I am working with is PostgreSQL 8.1.21.

The CSV file upload is done by the client and it has a date column. The date sometimes comes as '28-Sep-13' and sometimes as '28/09/2013' formats.

I tried to use the following to cast the string into timestamp: str_date::timestamp.

This works fine if str_date is something like '28-Sep-13' but it won't work if the incoming date has the format '28/09/2013', when this error occurs:

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

Basically the client keeps changing the date format in the uploaded CSV file.
Is there a way to convert the date strings into timestamp depending on its actual format?

Answer 1

You need to set your datestyle to "ISO, DMY". It is set to "ISO, MDY" by default, and would cause your example to fail:

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

(examples done in PostgreSQL 9.1, but the DateStyle setting and associated behavior are ancient, so should work fine)

Answer 2

You can circumvent the problem with these steps:

1. Create an empty temporary table with the same structure as target table:

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

2. Change the type of the problematic column to text:

   ALTER TABLE tmp ALTER COLUMN str_date TYPE text;
   

3. Import data to the temp table. Should work fine now:

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

4. INSERT into target table depending on depending on the actual content of the column:

   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