Преобразовать странный формат даты и времени

Содержание

Вопрос или проблема
Ответ или решение
Конвертация нестандартного формата даты и времени в MySQL
Проблема
Решение
Комментарии к SQL запросу
Дополнительная оптимизация
Заключение

Вопрос или проблема

В одной из моих таблиц MySQL 5.7 есть столбец с необычным форматом даты и времени. Он выглядит так:

2024-08-09 20:57:31.4477

Я пытаюсь использовать этот запрос, чтобы преобразовать его в формат даты m/d/Y. Вот что я сейчас пробую:

update table set `column1` = 
CASE column1
    WHEN '' THEN NULL 
    ELSE date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s %P'), '%m/%d/%Y')
END;

Но я получаю следующую ошибку:

Некорректное значение даты и времени: '2024-08-09 20:57:31.4477' для функции str_to_date

При попытке сравнить столбец даты и времени я также использовал этот формат: '%Y-%m-%d %H:%i:%s %p', но получаю ту же ошибку.

Какой формат мне следует использовать в str_to_date для вышеуказанной даты и времени?

Я попытался использовать эти форматы:

'%Y-%m-%d %H:%i:%s.%P'
'%Y-%m-%d %H:%i:%s.%p'
'%Y-%m-%d %H:%i:%s.%s'

Все равно получаю ту же ошибку.

Последние четыре цифры в этом примере строки даты “2024-08-09 20:57:31.4477” представляют собой дробную часть секунд, т.е. упоминается 32.4477 секунды, или иначе говоря, 32 секунды и 447700 микросекунд. Правильный способ указать, что у вас есть дробная часть для компонента секунд в вашей входной строке, это %f.

Обратите внимание, что нет спецификатора %P, а спецификатор %p относится к AM/PM. См. документацию SQL 5.7 по функциям даты и времени.

Так что используйте '%Y-%m-%d %H:%i:%s.%f' как строку формата для функции str_to_date.

Я сталкивался с такими же проблемами ранее из-за длины битового поля даты и времени. При проектировании таблицы вы должны минимизировать размер значения поля даты и времени. Если вы не хотите менять дизайн таблицы, вы можете попробовать это.

UPDATE table_name 
SET column1 = 
    CASE 
        WHEN column1 = '' THEN NULL 
        ELSE DATE_FORMAT(
            STR_TO_DATE(SUBSTRING_INDEX(column1, '.', 1), '%Y-%m-%d %H:%i:%s'), 
            '%m/%d/%Y'
        )
    END;

Ответ или решение

Конвертация нестандартного формата даты и времени в MySQL

В процессе работы с базами данных, разработчики иногда сталкиваются с нестандартными форматами даты и времени. В вашем случае в столбце базы данных MySQL 5.7 используется формат, который включает в себя доли секунды: 2024-08-09 20:57:31.4477. Вам необходимо преобразовать его в формат m/d/Y, и это может оказаться непростой задачей. Давайте рассмотрим решение данной проблемы более подробно.

Проблема

Ваш текущий запрос для обновления данных вызывает ошибку:

Incorrect datetime value: '2024-08-09 20:57:31.4477' for function str_to_date

Это связано с тем, что вы используете неправильный формат при вызове функции STR_TO_DATE. Формат, который вы пробовали, не включает корректного обозначения долей секунды. В MySQL для обозначения дробной части секунды необходимо использовать спецификатор %f.

Решение

Для успешного преобразования даты и времени вам потребуется использовать следующий подход:

Используйте спецификатор %f для дробной части секунды в вашей строке даты.
Убедитесь, что ваша конструкция учитывает возможность наличия пустых строк.

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

UPDATE table_name 
SET column1 = 
    CASE 
        WHEN column1 = '' THEN NULL 
        ELSE DATE_FORMAT(
            STR_TO_DATE(column1, '%Y-%m-%d %H:%i:%s.%f'), 
            '%m/%d/%Y'
        )
    END;

Комментарии к SQL запросу

CASE конструкция: Здесь мы проверяем, является ли значение в column1 пустым. Если да, то устанавливаем значение NULL, что упростит обработку данных.
STR_TO_DATE: Функция преобразует строку в формат даты и времени, где:
- %Y-%m-%d — год, месяц, день
- %H:%i:%s — часы, минуты, секунды
- %f — дробная часть секунды
DATE_FORMAT: После успешного преобразования, функция DATE_FORMAT позволяет отформатировать дату в нужный вам формат.

Дополнительная оптимизация

Если существуют проблемы с полем длины DATETIME, и вам не нужно сохранять доли секунды, можно изменить запрашиваемый формат с использованием SUBSTRING_INDEX для отбора целой части времени:

UPDATE table_name 
SET column1 = 
    CASE 
        WHEN column1 = '' THEN NULL 
        ELSE DATE_FORMAT(
            STR_TO_DATE(SUBSTRING_INDEX(column1, '.', 1), '%Y-%m-%d %H:%i:%s'), 
            '%m/%d/%Y'
        )
    END;

В данном случае SUBSTRING_INDEX(column1, '.', 1) извлекает только ту часть времени, которая необходима для преобразования.

Заключение

Этот подход позволяет эффективно и точно конвертировать нестандартные форматы даты и времени в MySQL, обеспечивая корректное преобразование и минимизируя возможность возникновения ошибок. Правильный выбор спецификаторов в функциях работы с датой и временем — это важный аспект для успешного управления данными в вашей базе.