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?

First of all: consider upgrading. [PostgreSQL **8.1** was released in 2005 and has reached end of life a year ago](http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy). It is horribly outdated. — Erwin Brandstetter, Nov 15 '11 at 02:13

score 17 · Accepted Answer · answered Nov 15 '11 at 19:47

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)

Thanks Matthew for the help. I added set datestyle = 'ISO, DMY' to the query and problem is solved. — Shiver, Nov 16 '11 at 02:24
Glad to hear it fixed the problem. If you could be so kind as to mark the answer accepted, it would be appreciated! — Matthew Wood, Dec 02 '11 at 16:39

Erwin Brandstetter · Answer 2 · 2015-03-19T13:19:26.140

You can circumvent the problem with these steps:

Create an empty temporary table with the same structure as target table:
```
CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;
```

Change the type of the problematic column to text:

ALTER TABLE tmp ALTER COLUMN str_date TYPE text;

Import data to the temp table. Should work fine now:
```
COPY tmp FROM '/path/to/my/file.txt';
```

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

score 1 · Answer 3 · answered Nov 15 '11 at 09:12

I agree with Erwin, but I would try create database function (PL/pgSQL, PL/Python or other language) that can convert various date strings into date. In Erwins answer you can see WHEN ... THEN and you can use it. Such function will be easier to test and maintain.

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

3 Answers3

Linked