Использование MySQL TIMESTAMP против непосредственного хранения временных меток

У меня дилемма сохранения значений даты и времени в формате MySQL TIMESTAMP по сравнению с пользовательским форматом UNSIGNED INT. Основными соображениями здесь являются скорость извлечения, соответствующие вычисления диапазона в PHP и случайное форматирование в удобочитаемые значения.

Место для хранения, необходимое для каждого типа и их диапазонов:

DATETIME        8 bytes  '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP       4 bytes  '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
UNSIGNED INT    4 bytes  (Maximum Value 4294967295)

Мне вообще не нужен диапазон DATETIME. Я разрываюсь между TIMESTAMP и UNSIGNED INT.

Аргументы в пользу UNSIGNED INT:

  • Временная метка UNIX 4294967295 преобразуется в Sun, 07 февраля 2106 06:28:15 GMT, что больше, чем TIMESTAMP, и достаточно хорошо для меня
  • Сравнение этих временных меток непосредственно в PHP будет быстрее, чем преобразование TIMESTAMP через strtotime () и последующее их сравнение.

Единственное преимущество, которое дает мне TIMESTAMP, - это когда я вручную читаю значения из таблицы mysql и мне нужно их «увидеть».

Есть ли веские причины использовать TIMESTAMP, а не UNSIGNED INT?


mysql php timestamp datetime unsigned-integer
person siliconpi    schedule 11.08.2011    source источник
comment
См. Также: datetime vs timestamp?   -  person JYelton    schedule 11.08.2011
comment
32-битная временная метка будет доступна только до января 2038 года, и многие библиотеки по-прежнему используют 32-битный time_t, а не 64-битный, поэтому вы можете столкнуться с проблемами переносимости. На внутреннем уровне PHP в любом случае хранит метки времени / даты и времени в числовом формате и преобразует их только в красивые строки типа yyyy-mm-dd при извлечении.   -  person Marc B    schedule 11.08.2011

Ответы (4)


arrow_upward
30
arrow_downward

Аргументы в пользу TIMESTAMP

  • Он неявно хранит данные в часовом поясе UTC. Независимо от часового пояса вашего сеанса. Полезно, если вам нужно использовать разные часовые пояса.
  • Вы можете иметь автоматические столбцы с отметками времени, используя DEFAULT CURRENT_TIMESTAMP или ON UPDATE CURRENT_TIMESTAMP (один столбец на таблицу только до MySQL 5.6.5)
  • Вы можете использовать функцию datetime для сравнения дат, сложения, вычитания, поиска по диапазону и т. Д. Без необходимости использования функции FROM_UNIXTIME() - это упростит написание запросов, которые могут использовать индексы.

  • В PHP

    >> date('Y-m-d h:i:s',4294967295);
'1969-12-31 11:59:59'

    так что диапазон на самом деле тот же

Когда UNIX_TIMESTAMP () используется в столбце TIMESTAMP, функция возвращает значение внутренней метки времени напрямую, без неявного преобразования «строка в метку времени Unix».

person Mchl    schedule 11.08.2011
comment
echo date('Y-m-d h:i:s',4294967295); у меня выдает 2106-02-07 06:28:15 - вы используете 32-битную ОС? - person Tim Fountain; 11.08.2011
comment
Нет, это 64-битная Windows, но я думаю, что у меня работает 32-битный PHP. Это то, что вам нужно принять во внимание, если вы хотите, чтобы ваше приложение было переносимым. Если у вас будет контроль над производственной средой, то неважно. - person Mchl; 11.08.2011
comment
Что касается вашего второго маркера, только один столбец может быть сделан DEFAULT CURRENT_TIMESTAMP или ON UPDATE CURRENT_TIMESTAMP, как указано здесь (по крайней мере, в MySQL v5.1): dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html - person Boaz Rymland; 23.04.2012
comment
@ Боаз Римланд: Верно. Это меняется в MySQL 5.6.5, который также добавляет значения автозапуска для типа данных DATETIME. dev.mysql.com/doc/refman/5.6/en/ timestamp-initialization.html - person Mchl; 28.04.2012
comment
Разве временные метки не должны храниться в формате UTC? - person JoeTidee; 18.08.2015
comment
Они есть. В ответе выше я написал, что это время по Гринвичу, но на самом деле это UTC. - person Mchl; 18.08.2015

arrow_upward
9
arrow_downward

Единственное реальное использование TIMESTAMP - это когда вы хотите, чтобы это поле обновлялось автоматически при обновлении строки (что является поведением по умолчанию для этого поля) или когда требования к хранилищу данных настолько строги, что 4 байта на строку действительно имеют значение для ты.

На самом деле сравнение должно быть между DATETIME и UNSIGNED INT, и я бы рекомендовал DATETIME, потому что:

  • Вы можете использовать собственные функции даты / времени MySQL для выбора по диапазонам дат и т. Д.
  • Тривиально легко выбрать эти даты в качестве временных меток UNIX для удобного форматирования в PHP: SELECT UNIX_TIMESTAMP(field) FROM table, не нужно выбирать исходное значение и использовать strtotime
  • Легче читать и редактировать поля в вашей базе данных напрямую, если вам нужно (как вы указали).
  • Нет ограничений по диапазону дат

На мой взгляд, один только пункт два действительно устраняет любые причины для хранения в целых числах.

person Tim Fountain    schedule 11.08.2011
comment
Обратной стороной DATETIME является то, что он не сохраняет смещение по Гринвичу в поле. Вам нужно либо преобразовать все, прежде чем сохранять в GMT (или любое другое время), а затем преобразовать его обратно, если вы имеете дело с несколькими часовыми поясами. Сохранение в INT устраняет некоторые из этих требований. - person ashurexm; 11.08.2011
comment
Положительным моментом для DATETIME является то, что я ленив, и он неплохо работает для одной локали / часового пояса. - person ashurexm; 11.08.2011
comment
Спасибо за комментарий к UNIX_TIMESTAMP - я не знал об этом раньше. - person Niels Abildgaard; 01.05.2013
comment
Я хранил все как unsigned int с библиотекой Carbon DateTime ... это интересная информация для рассмотрения. +1! - person Jethro Hazelhurst; 09.11.2016
comment
Как вы справляетесь с переходом на летнее время, если используете DATETIME? Когда мы перематываем часы на час назад, мы возвращаемся к тому же времени, что и часом ранее. У вас нет такой потенциальной проблемы со временем Unix. - person Peppe L-G; 11.05.2018
comment
Я считаю, что лучше всего хранить все в формате UTC. Если вы храните местные часовые пояса, вы также должны иметь возможность определять часовой пояс для обработки разницы во времени и перехода на летнее время. Я думаю, что если вы сохраните значения DATETIME из нескольких локальных часовых поясов и каким-то образом не сможете получить часовой пояс, у вас будут гораздо большие проблемы, чем при переходе на летнее время. - person ; 15.03.2019

arrow_upward
5
arrow_downward

Возможно, это не «научный» ответ, но я всегда сбиваю с толку способ обработки MySql преобразования, арифметики, сравнения и т. Д. В столбцах TIMESTAMP. Столбец UNSIGNED INT гораздо проще, и я всегда знаю, чего ожидать.

P.S. Возможно, еще одна вещь в пользу столбца TIMESTAMP - это его способность автоматически устанавливать текущее время после каждого обновления или вставки, но это не то, без чего вы не можете жить.

person nobody    schedule 11.08.2011

arrow_upward
0
arrow_downward

Как всегда, это зависит от того, что вам нужно сохранить.
Например, если вы потребляете данные из некоторого API, и он отправляет вам время в виде числа (секунды, это обычное явление для рыночных данных), тогда может быть проще и быстрее просто сохранить его как unsigned int вместо того, чтобы каждый раз преобразовывать его в строку перед ее вставкой.

person Enrique    schedule 17.12.2020