Преобразование строки с другим форматом на сегодняшний день

У меня есть таблица со строковым столбцом (varchar(200)), который содержит дату в другом формате. Например

may 24 1983 12:00AM
1981-01-13 00:00:00
1979-01-13 00:00:00:123

Я хочу преобразовать этот столбец в дату, чтобы извлечь год. Как я мог сделать? Я использовал STR_TO_DATE, но не работает. Я должен использовать SUBSTRING для каждого другого формата?

MySQL версии 5.0.95


mysql sql date-conversion
person Luigi Saggese    schedule 04.10.2012    source источник
comment
Плохая схема. Плохой. Я бы сначала исправил данные (до согласованного преобразуемого текстового значения), а затем обновил бы схему, чтобы использовать реальный столбец даты и времени (и позволил бы значению автоматически преобразовываться или делать любую другую магию, необходимую для преобразования). Затем используйте стандартные функции данных для извлечения даты. Эта проблема существует только потому, что схема базы данных нарушена.   -  person    schedule 04.10.2012
comment
Мы не обсуждаем схему БД, и я согласен с вами, что это очень плохо. ГЛАВНЫЙ вопрос заключается в том, что у меня есть эта таблица как есть, и я хочу выполнить эту работу по очистке, которую я должен преобразовать в эти строки в дату и время. Как вы можете помочь? (с конструктивным комментарием)   -  person Luigi Saggese    schedule 04.10.2012

Ответы (2)


arrow_upward
5
arrow_downward

Есть трюк для определения действительной даты в справочная страница. Вы можете использовать его, чтобы определить, работает ли формат STR_TO_DATE.

select foo,
    case when length(date(str_to_date(foo,"%Y-%m-%d %H:%i:%S"))) is not null then str_to_date(foo,"%Y-%m-%d %H:%i:%S")
        when length(date(str_to_date(foo,"%b %d %Y %h:%i%p"))) is not null then str_to_date(foo,"%b %d %Y %h:%i%p")
    end as newdate
from my_table

Поместите один формат для всех, кого вы ожидаете. Тестируйте как сумасшедший.

Удачи.

(О, и поздравляю с попыткой очистить плохую схему!)

person Alain Collins    schedule 04.10.2012
comment
Хочешь узнать дальнейшее осложнение? Аббревиатуры названия месяцев не на английском языке! Посоветуете ли вы мне убрать аббревиатуры (например, итальянский - английский gen = jan)? - person Luigi Saggese; 05.10.2012
comment
Если аббревиатуры есть в куче разных языков, то поможет их стандартизация. Если они в одном (скажем, итальянском), мне было бы интересно, покроет ли это изменение языка по умолчанию в mysql для вас. - person Alain Collins; 05.10.2012

arrow_upward
0
arrow_downward

Я бы попытался сделать это, используя серверный язык, возможно, PHP, и использовать strotime для создания красивой даты.

В противном случае единственный другой вариант, который я вижу, - это начать писать CASEs для чтения возможных форматов и выводить, используя DATE_FORMAT. Возможно что-то вроде:

Псевдокод (для случая 24 мая 1983 г., 00:00)

CASE
    WHEN col (after first space is some digit) 
        AND (after second space is 4 digits)
        AND (after third space has 'AM' or 'PM')
    THEN
        DATE_FORMAT(col, '%....')

Вы также можете попробовать использовать REGEXP для сопоставления с образцом.

person Kermit    schedule 04.10.2012