Должен ли я использовать тип данных datetime или timestamp в MySQL?

в MySQL 5 и выше, метка значения преобразуются из текущего часового пояса в UTC для хранения и преобразуются обратно из UTC в текущий часовой пояс для извлечения. (Это происходит только для типа данных TIMESTAMP и не для других типов, таких как DATETIME.)

по умолчанию, текущий часовой пояс для каждого соединения является время сервера. Часовой пояс может быть установлен на основе каждого соединения, как описано в Время Сервера MySQL Поддержка Зоны.

Я всегда использую поля DATETIME для чего-либо, кроме метаданных строк (дата создания или изменения).

Как указано в документации MySQL:

тип DATETIME используется, когда вам нужны значения, содержащие информацию о дате и времени. MySQL извлекает и отображает значения DATETIME в формате' YYYY-MM-DD HH:MM:SS'. Поддерживаемый диапазон-от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'.

...

в Тип данных TIMESTAMP имеет диапазон от '1970-01-01 00:00:01' UTC до '2038-01-09 03:14: 07' UTC. Он имеет различные свойства, в зависимости от версии MySQL и SQL Server работает в режиме.

вы, скорее всего, попадете в нижний предел по меткам времени в общем использовании-например, хранение даты рождения.

ниже примеры показывают, как TIMESTAMP тип даты изменил значения после изменения time-zone to 'america/new_york' здесь DATETIMEостается неизменной.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

я преобразовал свой ответ в статью, чтобы больше людей могли найти это полезным,MySQL: Типы Данных Datetime И Timestamp.

основное различие заключается в том, что DATETIME является постоянным, а временная метка зависит от time_zone настройка.

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

простыми словами: если у меня есть база данных в Австралии, и взять дамп этой базы данных для синхронизации/заполнения базы данных в Америке, то метка времени будет обновляться, чтобы отразить реальное время события в новом часовом поясе, в то время как DATETIME будет по-прежнему отражать время события в часовом поясе au.

отличный пример использования DATETIME, где должна была использоваться временная метка, находится в Facebook, где их серверы никогда не уверены, что время произошло в разных часовых поясах. Однажды у меня был разговор, в котором время сказало, что я отвечал на сообщения до того, как сообщение было фактически отправлено. (Это, конечно, также могло быть вызвано плохим переводом часового пояса в программном обеспечении для обмена сообщениями, если время было опубликовано, а не синхронизировано.)

Я принимаю это решение на семантической основе.

Я использую метку времени, когда мне нужно записать (более или менее) фиксированный момент времени. Например, когда запись была вставлена в базу данных или когда произошло какое-то действие пользователя.

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

метка времени составляет 4 байта против 8 байт для DATETIME.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

но, как сказал скронид, у него есть нижний предел 1970 года. Это здорово для всего, что может произойти в будущем, хотя;)

1. метка времени-это четыре байта против восьми байтов для DATETIME.

2. метки времени также легче в базе данных и индексируются быстрее.

3. тип DATETIME используется, когда вам нужны значения, содержащие информацию о дате и времени. MySQL извлекает и отображает значения DATETIME в формате’ YYYY-MM-DD HH:MM:SS'. Поддерживаемый диапазон-от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'.

метка времени тип данных имеет диапазон от '1970-01-01 00:00:01' UTC до '2038-01-09 03:14:07' мирового. Он имеет различные свойства, в зависимости от версии MySQL и SQL Server работает в режиме.

1. DATETIME является постоянным, в то время как отметка времени осуществляется с помощью параметра time_zone.

Я рекомендую использовать ни поле DATETIME или TIMESTAMP. Если вы хотите представить определенный день в целом (например, День рождения), то используйте тип даты, но если вы более конкретны,вы,вероятно,заинтересованы в записи фактического момента, а не единицы времени (день, неделя, месяц, год). Вместо того, чтобы использовать DATETIME или TIMESTAMP, используйте BIGINT и просто сохраните количество миллисекунд с момента эпохи (System.currentTimeMillis() если вы используете Ява.) Это имеет несколько преимуществ:

1. вы избегаете привязки к конкретному поставщику. Почти каждая база данных поддерживает целые числа относительно аналогичным образом. Предположим, вы хотите перейти в другую базу данных. Вы хотите беспокоиться о различиях между значениями DATETIME MySQL и тем, как их определяет Oracle? Даже среди различных версий MySQL временные метки имеют разный уровень точности. Это было только недавно, что MySQL поддерживал миллисекунды в метках времени.
2. нет проблем с часовым поясом. Здесь были некоторые проницательные комментарии о том, что происходит с часовыми поясами с различными типами данных. Но является ли это общим знанием, и все ли ваши сотрудники найдут время, чтобы изучить его? С другой стороны, довольно сложно испортить изменение BigINT в java.утиль.Дата. Использование BIGINT вызывает много проблем с часовыми поясами, чтобы упасть на обочину.
3. Не беспокойтесь о диапазоне и точности. Вам не нужно беспокоиться о том, что быть сокращение по будущим диапазонам дат (метка времени идет только до 2038 года).
4. интеграция сторонних инструментов. Используя целое число, это тривиально для сторонних инструментов (например, EclipseLink) для взаимодействия с базой данных. Не каждый сторонний инструмент будет иметь такое же понимание "datetime", как и MySQL. Хотите попробовать и выяснить в спящем режиме, следует ли использовать java.язык SQL.Метка времени или java.утиль.Объект даты если вы используете эти пользовательские типы данных? Использование базовых типов данных используйте с помощью сторонних инструментов тривиально.

эта проблема тесно связана с тем, как вы должны хранить денежную ценность (т. е. $1.99) в базе данных. Следует ли использовать десятичное число, или денежный тип базы данных, или, что хуже всего, двойной? Все 3 из этих вариантов ужасны, по многим из тех же причин, перечисленных выше. Решение состоит в том, чтобы хранить стоимость денег в центах с помощью BIGINT, а затем конвертировать центы в доллары при отображении значения пользователю. Задача базы данных-хранить данные, а не для того, чтобы отважиться на эти данные. Все эти причудливые типы данных, которые вы видите в базах данных(особенно Oracle), мало добавляют и начинают вас по дороге к блокировке поставщика.

зависит от приложения, на самом деле.

рассмотреть вопрос об установлении метки пользователя к серверу в Нью-Йорк, на встречу в Шанхае. Теперь, когда пользователь подключается в Sanghai, он получает доступ к той же метке времени встречи с зеркального сервера в Токио. Он увидит назначение по токийскому времени, смещенное от первоначального Нью-Йоркского времени.

поэтому для значений, которые представляют время пользователя, как назначение или расписание, datetime лучше. Она позволяет потребителю контролировать точную дату и желаемое время, независимо от настроек сервера. Установленное время-это установленное время, не зависящее от часового пояса сервера, часового пояса пользователя или от изменений в способе расчета летнего времени (да, оно меняется).

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

метки времени также легче в базе данных и индексируются быстрее.

2016 +: я советую установить ваш часовой пояс Mysql в UTC и использовать DATETIME:

любые последние передние рамки (угловые 1/2, react, Vue,...) можно легко и автоматически конвертировать UTC datetime в местное время.

дополнительно:

• DATETIME теперь может быть автоматически установить текущее значение времени Как установить значение по умолчанию для столбца MySQL Datetime?

A timestamp поле является частным случаем

отметка времени всегда находится в UTC (т. е. прошло секунд с 1970-01-01, в UTC), и ваш сервер MySQL автоматически преобразует его в дату/время для часового пояса сервера. В долгосрочной перспективе отметка времени-это путь, потому что вы знаете, что ваши временные данные всегда будут в UTC. Например, вы не испортите свои даты, если вы перейдете на другой сервер или измените настройки часового пояса на своем сервере.

сравнение между DATETIME, TIMESTAMP и DATE

что это [.фракция]?

• значение DATETIME или TIMESTAMP может включать конечную дробную часть секунд до микросекунд (6 цифр) точность. В в частности, любая дробная часть в значении, вставленном в DATETIME или столбец TIMESTAMP хранится, а не отбрасывается. Это конечно необязательный.

источники:

• MySQL Дата / Время ссылки на типы данных
• ссылка на требования к хранилищу MySQL

стоит отметить, что в MySQL вы можете использовать что-то по строкам ниже при создании столбцов таблицы:

on update CURRENT_TIMESTAMP

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

Я всегда буду использовать метку времени Unix при работе с MySQL и PHP. Основной причиной этого является значение по умолчанию дата метод в PHP использует метку времени в качестве параметра, поэтому разбор не требуется.

чтобы получить текущую метку времени Unix в PHP, просто делать time();
а в MySQL делают SELECT UNIX_TIMESTAMP();.

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

например,user таблицы ДАТА РЕГИСТРАЦИИ

тип данных timestamp хранит дату и время, но в формате UTC, а не в текущем формате часового пояса, как это делает datetime. И когда вы получаете данные, timestamp снова преобразует их в текущее время часового пояса.

Итак, предположим, что вы находитесь в США и получаете данные с сервера, который имеет часовой пояс США. Затем вы получите дату и время в соответствии с часовым поясом США. Столбец типа данных timestamp всегда обновляется автоматически при обновлении его строки. Так что это может быть полезно отслеживать, когда конкретная строка была обновлена в последний раз.

для более подробной информации вы можете прочитать в блоге Timestamp Vs Datetime .

Я всегда использую метку времени Unix, просто чтобы поддерживать здравомыслие при работе с большим количеством информации о датах и времени, особенно при выполнении настроек для часовых поясов, добавлении/вычитании дат и т. п. При сравнении временных меток это исключает усложняющие факторы часового пояса и позволяет сэкономить ресурсы при обработке на стороне сервера (будь то код приложения или запросы к базе данных), поскольку вы используете легкую арифметику, а не более тяжелую дату-время добавления / вычитания функции.

еще одна вещь, которую стоит рассмотреть:

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

основное различие составляет

• индекс на отметке времени-работает
• индекс на Datetime -не работает

смотреть на это сообщение, чтобы увидеть проблемы с индексированием Datetime

остерегайтесь изменения метки времени при выполнении инструкции UPDATE в таблице. Если у вас есть таблица со столбцами ' Name '(varchar),' Age '(int) и' Date_Added ' (timestamp) и вы запускаете следующую инструкцию DML

UPDATE table
SET age = 30

тогда каждое значение в вашем столбце "Date_Added" будет изменено на текущую метку времени.

еще одна разница между отметкой времени и Datetime заключается в отметке времени, которую вы не можете использовать по умолчанию для NULL.

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

Он может отслеживать в разных часовых поясах, поэтому, если вам нужно отобразить относительное время, например, время UTC-это то, что вы хотите.

ссылка взята из этой статьи:

основные отличия:

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

отметка времени также зависит от различных настроек, связанных с часовым поясом. Дата и время постоянны.

отметка времени внутренне преобразуется в текущий часовой пояс UTC для хранения и во время извлечения преобразуется обратно в текущий часовой пояс. DATETIME не может этого сделать.

поддержанный ряд отметки времени: '1970-01-01 00: 00: 01' UTC to '2038-01-19 03:14: 07' UTC Диапазон, поддерживаемый DATETIME: '1000-01-01 00: 00: 00' to '9999-12-31 23:59:59'

Я предпочитаю использовать метку времени, чтобы сохранить все в одном общем формате raw и форматировать данные в PHP-коде или в вашем SQL-запросе. Есть случаи, когда это удобно в вашем коде, чтобы сохранить все в простых секундах.

в моем случае я установил UTC в качестве часового пояса для всего: системы, сервера базы данных и т. д. каждый раз, когда я могу. Если мой клиент требует другой часовой пояс, то я настроить его на приложение.

Я почти всегда предпочитаю временные метки, а не поля datetime, потому что временные метки неявно включают часовой пояс. Итак, с момента, когда приложение будет доступно пользователям из разных часовых поясов, и вы хотите, чтобы они видели даты и время в своем местном часовом поясе, это поле тип делает это довольно легко сделать, чем если бы данные были сохранены в полях datetime.

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

Итак, подводя итог, я ценю это преимущества timestamp:

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

по всем этим причинам я выбираю поля UTC & timestamp, где возможно. И я избегаю головной боли;)

Мне нравится временная метка Unix, потому что вы можете конвертировать в числа и просто беспокоиться о числе. Плюс вы добавляете / вычитаете и получаете длительность и т. д. Затем конвертировать результат в любом формате. Этот код определяет, сколько времени в минутах прошло между отметкой времени из документа и текущим временем.

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

A TIMESTAMP требуется 4 байта, тогда как A DATETIME требуется 8 байт.

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

Я просто использую unsigned BIGINT при сохранении UTC ...

который затем все еще может быть настроен на местное время в PHP.

the DATETIME выбирается с помощью FROM_UNIXTIME( integer_timestamp_column ).

очевидно, что нужно установить индекс для этого столбца, иначе не было бы никакого продвижения.