Как преобразовать строки даты в метку времени, не зная формат даты

Я пытаюсь написать запрос, чтобы вставить значение в поле типа timestamp with no timezone data. Значение происходит из файла CSV.

В версии, с которой я работаю, PostgreSQL 8.1.21.

Загрузка CSV файла выполняется клиентом и имеет столбец даты. Дата иногда встречается как '28-Sep-13', а иногда и как '28/09/2013'.

Я попытался использовать следующее, чтобы вывести строку в метку времени: str_date::timestamp.

Это отлично работает, если str_date - это что-то вроде '28-Sep-13', но оно не будет работать, если входящая дата имеет формат '28/09/2013', когда возникает эта ошибка:

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

В основном клиент продолжает изменять формат даты в загруженном CSV файле.
Есть ли способ преобразовать строки даты в метку времени в зависимости от ее фактического формата?

Ответ 1

Вам нужно установить свой datestyle в "ISO, DMY". По умолчанию установлено значение "ISO, MDY" и приведет к сбою вашего примера:

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

(примеры, сделанные в PostgreSQL 9.1, но параметр DateStyle и связанное с ним поведение являются древними, поэтому должны работать нормально)

Ответ 2

Вы можете обойти проблему с помощью следующих шагов:

Создайте пустую временную таблицу с той же структурой, что и целевая таблица:
```
CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;
```
Измените тип проблемного столбца на текст:
```
ALTER TABLE tmp ALTER COLUMN str_date TYPE text;
```
Импортировать данные в таблицу temp. Должен теперь работать нормально:
```
COPY tmp FROM '/path/to/my/file.txt';
```

INSERT в целевую таблицу в зависимости от фактического содержимого столбца:

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

Я согласен с Erwin, но я бы попытался создать функцию базы данных (PL/pgSQL, PL/Python или другой язык), который может преобразовывать различные строки даты в date. В ответе Эрвинса вы можете видеть WHEN ... THEN, и вы можете использовать его. Такая функция будет легче протестировать и поддерживать.