Postgres SQL - выберите несовместимые форматы даты из символьного поля

Я застрял в вопросе форматирования даты и надеюсь, что кто-то может мне помочь!

В нашей БД есть поле даты, которое было отформатировано нашей командой программистов как символьное поле из-за разных источников/форматов импорта.

В идеале я стремлюсь создать представление, которое заканчивается только датой, которую я затем буду использовать в инструменте отчетности (dbxtra) для ежедневной/ежемесячной группировки сводных данных и отчетов.

Пример данных:

"2015-05-30"

"3/06/2015 12:00 AM"

Я пытался:

  • преобразование в дату с ::date

  • substr взять первые 10 символов

Какие-либо предложения?


postgresql date format sqldatatypes
person Damo    schedule 12.06.2015    source источник
comment
Я также пробовал: обрезать (оба '' из substr (comp_date, 1,10)) как comp_date2, который теперь дает мне часть даты только без пробелов.... хотя все еще бесполезно   -  person Damo    schedule 12.06.2015
comment
Используйте 1_. Поскольку форматы в столбце различаются, вам, вероятно, понадобится оператор CASE, который пробует разные форматы. Это будет медленно и некрасиво. Я предлагаю сильно ударить вашу команду программистов по голове.   -  person Craig Ringer    schedule 12.06.2015
comment
И как только вы уберете беспорядок, измените столбец на timestamp   -  person a_horse_with_no_name    schedule 12.06.2015

Ответы (1)


arrow_upward
0
arrow_downward

Я бы написал для этого хранимую процедуру, которую вы можете вызвать в своем sql-запросе. Что-то вроде этого (псевдокод):

CREATE OR REPLACE FUNCTION convertMessedUpStringsToDate(i_val1 varchar) RETURNS timestamp
declare 
  retval timestamp;
  formatArray varchar[];
begin
  -- build array with possible formats, most used in front
  formatArray[0] = 'YYYY-MM-DD';
  formatArray[1] = 'DD/MM/YYYY HH12:MI AM';

  for i in 0 .. formatArray.count loop
    begin
      retval = to_timestamp(i_val, formatArray[i]);
    exception
      -- catch any exception and try the next format
    end;
  end loop;

  return retval;
end;

Другой вариант — проанализировать ввод с помощью регулярного выражения и таким образом найти соответствующий шаблон. В любом случае, я надеюсь, что у вас не слишком много разных шаблонов.

person Robe Elckers    schedule 12.06.2015