Возможна ли массовая конвертация datetime в timestamp?
Возможно знатоки Mysql могут подсказать: существует ли какой SQL запрос для того, чтобы даты, хранящиеся в одной таблице в строковом типе, перенести в новую таблицу, изменив формат даты на unix timestamp? Есть база данных wordpress, с огромнейшим количеством публикаций, дата которых хранится в таблице post_date, и имеет вид "2011-08-19 07:41:59". Перенёс проект на другой движок. Месяц переносил пользователей, тексты и комментарии. Теперь у всего этого нужно перенести даты. Дата в новом движке хранится в формате unix timestamp, в столбце имеющем тип INT. Публикаций и комментариев тысячи. Вручную отдельно менять в базе каждую дату, прогоняя её через онлайн-конвертер - задачка малоприятная и чрезвычайно долгая. Есть ли более удобное решение?
В MySQL разница между DATETIME и TIMESTAMP одна-единственная. При получении DATETIME возвращается ровно то, что хранится, а при получении TIMESTAMP значение интерпретируется как UTC и приводится к зоне времени сервера.
Изменение типа данных с DATETIME на TIMESTAMP выполняется мгновенно, поскольку это онлайновая операция, изменяющая только метаданные таблицы и не затрагивающая хранимые данные.
=========================
И всё гораздо хуже, если под "Дата в новом движке хранится в формате timestamp" разумеется хранение в UNIX TIMESTAMP в поле типа INT/BIGINT.
Тогда хреновше. На месте не делается. Придётся идти обычным порядком. Создаётся новое поле INT, затем туда копируются данные с конвертацией, затем старое поле убивается, а новое переименуется, и на него натягиваются все индексы, констрейнты и прочая шелуха.
Akina, всё лучше, чем вручную то забивать. Про новое поле это более-менее понятно. А вот как сам запрос выглядеть должен?
Есть у меня, скажем, таблица wp_posts, где столбец строкового типа post_date содержит даты публикаций. И в той же базе данных есть таблица alx_blogs, где столбец типа INT postdate хранит даты в UNIX TIMESTAMP. Часть этих дат заполнена, часть отсутсвует. И нужно мне из таблицы wp_posts, перенести даты в таблицу alx_blogs, преобразовав их соответственно в UNIX TIMESTAMP из строкового типа.
Akina, на будущее объясните чайнику что за связь имеется ввиду? "Смилуйтесь, ваше сиятельство! Языками не владею". За запрос спасибо, добрый человек! Благодаря вам я наконец-то могу приступить уже к творчеству, а не править еженощно эти даты. Это было то, что нужно.
Для тех же, у кого возникнут такие же вопросы как у меня, внесу дополнение в запрос. Нужно конечно же указать в запросе, что id поста в новой таблице должно равняться id поста в старой таблице. Примерно так должно выглядеть:
UPDATE stal_posts A, wp_sfposts B SET A.post_time = UNIX_TIMESTAMP(B.post_date) WHERE A.post_id = B.post_id;
Dmitry Bay, не поверите, но мне на решение этой проблемы не хватило и недели. Сижу, потихоньку вбиваю руками. Максимум, чего мне удалось добиться, это в колонке с timestamp появился только год публикации. Проблема собственно для меня в том как именно эту конвертацию запустить
Vitsliputsli, печаль в том, что ниже приведённое решение, по видимому, не на мой случай. Данный запрос у меня всю колонку обнулил, проставив "0000-00-00 00:00:00".
Антон Шаманов, без разницы. Оба типа данных хранятся в UTC.
А если после конвертации софт выдаёт неверные данные - так это проблемы софта, который интерпретировал UTC (DATETIME хранит значение без зоны, что соответствует UTC) как некую иную зону.
DATETIME хранит значение без зоны, что соответствует UTC
нет, объясню на примере: есть 2 поля datetime, одно заполняется NOW() т.е. содержит текущее серверное время(вернее субд), а второе заполняется пользователем и содержит время с учетом зоны пользователя - эти два значения естественно будут отличатся если часовой пояс сервера/субд не совпадает с пользовательским.
это проблемы софта, который интерпретировал UTC
так автор и пытается избежать подобных проблем при конвертации
так автор и пытается избежать подобных проблем при конвертации
Да нету их, проблем при конвертации! Ну какая может быть проблема при выполнении UPDATE table SET newcolumn = UNIX_TIMESTAMP(old_column)?
Все проблемы начинаются после того, как клиент получает штамп времени из MySQL и начинает на стороне клиента (там, где работает PHP, JS, Java и пр.) своими немытыми руками преобразовывать полученные значения с учётом клиентской зоны. Пока тип времени был DATATIME - это было сравнительно безопасно. Но когда данные - это UNIX timestamp / INTEGER, любые ручные корректировки способны привести к потрясающим в своем идиотизме результатам. Это сродни перекодировкам в электронной почте... того же порядка проблемы.
Akina, проблема в том, что UNIX_TIMESTAMP сконвертирует datetime в timestamp взяв timezone указанный в настройках субд - если в проекте все даты в одной timezone, то можно без проблем скорректировать полученный timestamp, а если даты пишутся с учетом timezone пользователей, то задачка становится сложнее
Простой
