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

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

Поле timestamp - это частный случай поля datetime. Вы можете создавать столбцы timestamp, чтобы иметь специальные свойства; он может быть настроен на самообновление при создании и / или обновлении.

В терминах «больших» баз данных timestamp имеет несколько триггеров для особых случаев.

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

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

Если вы имели в виду, что хотите выбрать между использованием метки времени UNIX или собственного поля даты и времени MySQL, выберите собственный формат. Таким образом вы можете выполнять вычисления в MySQL ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)"), и просто изменить формат значения на временную метку UNIX ("SELECT UNIX_TIMESTAMP(my_datetime)"), когда вы запрашиваете запись, если вы хотите работать с ней с помощью PHP.

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

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

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

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

Как упомянул в документации MySQL:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

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

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

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

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

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

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

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

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

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

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

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

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

Примечание: часовой пояс соединения по умолчанию - это часовой пояс сервера, но его можно (нужно) изменять за сеанс (см. SET time_zone = ...).

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

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

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

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

Отметки времени также меньше в базе данных и быстрее индексируются.

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

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

Приведенные ниже примеры показывают, как тип даты 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.

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

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

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

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

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

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

on update CURRENT_TIMESTAMP

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

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

Например, рассмотрим таблицу user с полем ДАТА РЕГИСТРАЦИИ. В этой таблице user, если вы хотите узнать время последнего входа в систему конкретного пользователя, выберите поле типа отметка времени, чтобы поле обновлялось.

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

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

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

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

Для более подробной информации вы можете прочитать сообщение в блоге Отметка времени против даты и времени.

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

UPDATE table
SET age = 30

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

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

2. Отметки времени также меньше в базе данных и быстрее индексируются.

3. Тип DATETIME используется, когда вам нужны значения, содержащие информацию о дате и времени. MySQL извлекает и отображает значения DATETIME в формате «ГГГГ-ММ-ДД ЧЧ: ММ: СС». Поддерживаемый диапазон: от '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, в котором работает сервер.

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

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

Основное различие в том, что

• ИНДЕКС на отметке времени - работает
• ИНДЕКС по дате и времени - Не работает

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

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

1. Вы избегаете привязки к поставщику. Практически каждая база данных поддерживает целые числа относительно похожим образом. Предположим, вы хотите перейти в другую базу данных. Хотите ли вы беспокоиться о различиях между значениями DATETIME MySQL и тем, как Oracle их определяет? Даже среди разных версий MySQL TIMESTAMPS имеют разный уровень точности. Только недавно MySQL поддерживал миллисекунды в метках времени.
2. Нет проблем с часовым поясом. Здесь были содержательные комментарии о том, что происходит с часовыми поясами с разными типами данных. Но насколько это общеизвестно, и найдут ли все ваши коллеги время, чтобы изучить это? С другой стороны, довольно сложно испортить замену BigINT на java.util.Date. Использование BIGINT приводит к тому, что многие проблемы с часовыми поясами уходят на второй план.
3. Не беспокойтесь о диапазонах или точности. Вам не нужно беспокоиться о том, что будет сокращено в будущих диапазонах дат (TIMESTAMP идет только до 2038 года).
4. Интеграция сторонних инструментов. Использование целого числа тривиально для сторонних инструментов (например, EclipseLink) для взаимодействия с базой данных. Не все сторонние инструменты будут понимать «дату и время» так же, как MySQL. Хотите попробовать выяснить в Hibernate, следует ли использовать объект java.sql.TimeStamp или java.util.Date, если вы используете эти настраиваемые типы данных? Использование ваших базовых типов данных делает использование сторонних инструментов тривиальным.

Этот вопрос тесно связан с тем, как вы должны хранить денежную ценность (например, 1,99 доллара США) в базе данных. Что вы должны использовать: Decimal, или тип базы данных Money, или, что хуже всего, Double? Все три варианта ужасны по многим причинам, перечисленным выше. Решение состоит в том, чтобы хранить стоимость денег в центах с помощью BIGINT, а затем конвертировать центы в доллары, когда вы показываете значение пользователю. Работа базы данных - хранить данные, а НЕ интерпретировать эти данные. Все эти причудливые типы данных, которые вы видите в базах данных (особенно в Oracle), мало что добавляют и подталкивают вас к привязке к поставщику.

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

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

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

TIMESTAMP также зависит от других настроек, связанных с ЧАСОВЫМ ПОЯСОМ. DATETIME постоянно.

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

Поддерживаемый диапазон TIMESTAMP: «1970-01-01 00:00:01» UTC по «2038-01-19 03:14:07» UTC. Поддерживаемый диапазон DATETIME: С «1000-01-01 00:00:00» по «9999-12-31 23:59:59»

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

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

Кроме того:

• DATETIME теперь может быть установлен автоматически на текущее значение времени Как установить значение по умолчанию для столбца MySQL Datetime?
• Вопреки тому, что можно было подумать, DATETIME - это БЫСТРЕЕ THAN TIMESTAMP, http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/
• TIMESTAMP по-прежнему ограничен 1970-2038 гг.

(Если вы, вероятно, не измените часовой пояс своих серверов)

Пример с AngularJs

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

Все локализованные форматы времени доступны здесь: https://docs.angularjs.org/api/ng/filter/date

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

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

DATETIME должен быть выбран вместе с FROM_UNIXTIME( integer_timestamp_column ).

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

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

Многие ответы здесь предлагают хранить как временную метку, если вы должны представлять четко определенные моменты времени. Но вы также можете иметь точки во времени с datetime, если вы по соглашению храните их все в формате UTC.

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

Пока не упомянуто, что DEFAULT CURRENT_TIMESTAMP работает только с полями типа Timestamp, но не с полями типа DateTime.

Это становится актуальным для таблиц MS Access, которые могут использовать только DateTime, но не Timestamp.

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

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

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

Итак, подытоживая, я ценю следующие преимущества временной метки:

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

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

Сравнение DATETIME, TIMESTAMP и DATE

Что это за [.fraction]?

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

Источники:

• Справочник типов данных MySQL Date / Time
• Справочник требований к хранилищу MySQL

+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
|                                       TIMESTAMP                                       |                                 DATETIME                                 |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
| TIMESTAMP requires 4 bytes.                                                           | DATETIME requires 8 bytes.                                               |
| Timestamp is the number of seconds that have elapsed since January 1, 1970 00:00 UTC. | DATETIME is a text displays 'YYYY-MM-DD HH:MM:SS' format.                |
| TIMESTAMP supported range: ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC.    | DATETIME supported range: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′ |
| TIMESTAMP during retrieval converted back to the current time zone.                   | DATETIME can not do this.                                                |
| TIMESTAMP is used mostly for metadata i.e. row created/modified and audit purpose.    | DATETIME is used mostly for user-data.                                   |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+

timestamp - текущее время события, записанного компьютером через Сетевой протокол времени (NTP).

datetime - это текущий часовой пояс, установленный в вашей конфигурации PHP.

Я перестал использовать datetime в своих приложениях после того, как столкнулся с множеством проблем и ошибок, связанных с часовыми поясами. ИМХО использование timestamp лучше, чем datetime в большинстве случаев.

Когда вы спрашиваете, сколько времени? и ответ будет примерно таким: «2019-02-05 21:18:30», это не завершенный, не определенный ответ, потому что ему не хватает другой части, в каком часовом поясе? Вашингтон? Москва ? Пекин?

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

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

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

   SET time_zone = '+2:00';
   

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

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

Коротко

datetime = приложение поддерживает 1 часовой пояс (как для вставки, так и для выбора)

timestamp = приложение поддерживает любой часовой пояс (как для вставки, так и для выбора)

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

Если вы хотите ГАРАНТИРОВАТЬ, что ваше приложение НЕ будет работать в феврале 2038 года, используйте TIMESTAMP. Обратитесь к вашему REFMAN для получения информации о ДИАПАЗОНЕ поддерживаемых дат.

DATETIME не несет с собой информации о часовом поясе и всегда будет отображать то же самое независимо от часового пояса, действующего для сеанса, который по умолчанию соответствует часовому поясу сервера, если вы явно не изменили его. Однако, если я инициализирую столбец DATETIME с помощью функции, такой как NOW(), а не литерала, такого как '2020-01-16 12:15:00', то сохраненное значение, конечно же, будет текущей датой и временем, локализованными в часовом поясе сеанса.

TIMESTAMP, напротив, неявно переносит информацию о часовом поясе: когда вы инициализируете столбец TIMESTAMP значением, это значение преобразуется в UTC перед сохранением. Если сохраняемое значение является литералом, например '2020-01-16 12:15:00', оно интерпретируется как находящееся в текущем часовом поясе сеанса для целей преобразования. И наоборот, когда отображается столбец TIMESTAMP, он сначала будет преобразован из UTC в текущий часовой пояс сеанса.

Когда использовать то или другое? Пример из практики

Веб-сайт общественной театральной труппы представляет несколько спектаклей, билеты на которые продаются. Дата и время этих представлений появятся в раскрывающемся списке, из которого покупатель, желающий купить билеты на представление, выберет один. Было бы разумно, чтобы столбец базы данных performance_date_and_time был типа DATETIME. Если выступление проводится в Нью-Йорке, подразумевается, что здесь задействован неявный часовой пояс («местное время Нью-Йорка»), и в идеале мы хотели бы, чтобы дата и время отображались как «12 декабря 2019 года в 20:00». независимо от часового пояса сеанса и без необходимости выполнять какие-либо преобразования часового пояса.

С другой стороны, после начала выступления 12 декабря 2019 года в 20:00 мы, возможно, больше не захотим продавать билеты на него и, следовательно, больше не будем отображать это выступление в раскрывающемся списке. Итак, мы хотели бы знать, произошло ли '2019-12-12 20:00:00' или нет. Это было бы аргументом в пользу наличия столбца TIMESTAMP, установки часового пояса для сеанса на «Америка / Нью-Йорк» с set session time_zone='America/New_York' и последующего сохранения '2019-12-12 20:00:00' в столбце TIMESTAMP. Отныне мы можем проверить, началась ли производительность, сравнив этот столбец с NOW() независимо от часового пояса текущего сеанса.

Или может иметь смысл иметь столбцы DATETIME и TIMESTAMP для этих двух отдельных целей. Или нет. Ясно, что любой из них может служить обеим целям. Если вы используете только столбец DATETIME, то перед сравнением с NOW() вы должны установить текущий часовой пояс в соответствии с вашим местным часовым поясом. Если вы используете только столбец TIMESTAMP, вы должны установить часовой пояс сеанса на ваш местный часовой пояс перед отображением столбца.

Ни один. Типы DATETIME и TIMESTAMP принципиально не подходят для общих вариантов использования. MySQL изменит их в будущем. Вам следует использовать метки времени BIGINT и UNIX, если у вас нет особой причины использовать что-то еще.

Особые случаи

Вот некоторые конкретные ситуации, когда ваш выбор проще, и вам не нужны анализ и общие рекомендации в этом ответе.

• Только дата - если вас волнует только дата (например, дата следующего лунного Нового года, 25.02.2020) И у вас есть четкое представление о том, в каком часовом поясе применяется эта дата (или вам все равно, как в случае с Лунный Новый год), затем используйте тип столбца DATE.

• Запись времени вставки - если вы регистрируете дату / время вставки для строк в своей базе данных, И вам все равно, что ваше приложение сломается в следующие 19 лет, тогда продолжайте и используйте TIMESTAMP со значением по умолчанию CURRENT_TIMESTAMP().

Почему сломан TIMESTAMP?

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

Каждый раз, когда вы INSERT INTO или SELECT FROM столбец TIMESTAMP, учитывается физическое местоположение (т.е. конфигурация часового пояса) вашего клиента / сервера приложений. Если вы переместите сервер приложений, ваши даты сломаются ❌.

Почему сломан DATETIME?

DATETIME хранит DATE и TIME в одном столбце. Ни одна из этих вещей не имеет никакого значения, если часовой пояс не понятен, и часовой пояс нигде не сохраняется ❌. Вы должны указать предполагаемый часовой пояс в качестве комментария в столбце, потому что часовой пояс неразрывно связан с данными. Так мало людей используют комментарии к столбцам, поэтому это ожидаемая ошибка. Я унаследовал сервер из Аризоны, поэтому мне всегда нужно преобразовывать все временные метки из времени Аризоны, а затем в другое время.

Единственная ситуация, в которой DATETIME верна, - это завершить это предложение:

Your year 2020 solar new year starts at exactly DATETIME("2020-01-01 00:00:00").

Другого хорошего использования DATETIME нет. Возможно, вы представите себе веб-сервер для городского правительства в Делавэре. Конечно, можно предположить, что часовой пояс для этого сервера и всех людей, обращающихся к нему, находится в Делавэре, с Восточным часовым поясом, верно? Неправильный! В этом тысячелетии все мы думаем о серверах как о существующих в «облаке». Поэтому всегда неправильно думать о вашем сервере в каком-либо конкретном часовом поясе, потому что ваш сервер когда-нибудь будет перемещен.

Примечание: MySQL теперь поддерживает смещение часового пояса в литералах DATETIME (спасибо @Marko). Это может сделать установку DATETIME более удобной для вас, но не устраняет неполный и, следовательно, бесполезный смысл данных, обозначение этой фатальной проблемы («❌») выше.

Как использовать BIGINT?

Определять:

CREATE TEMPORARY TABLE good_times (
    a_time BIGINT
)

Вставьте конкретное значение:

INSERT INTO good_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

Или, конечно, это намного лучше из вашего приложения, например:

$statement = $myDB->prepare('INSERT INTO good_times VALUES (?)');
$statement->execute([$someTime->getTimestamp()]);

Выбирать:

SELECT a_time FROM good_times;

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

DATETIME vs TIMESTAMP:

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

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

TIMESTAMP также зависит от других настроек, связанных с ЧАСОВЫМ ПОЯСОМ. DATETIME постоянно.

TIMESTAMP внутренне преобразовал текущий часовой пояс в UTC для хранения, а во время извлечения преобразовал обратно в текущий часовой пояс.

DATETIME не может этого сделать.

TIMESTAMP составляет 4 байта, а DATETIME - 8 байтов.

Поддерживаемый диапазон TIMESTAMP: «1970-01-01 00:00:01» UTC по «2038-01-19 03:14:07» UTC. Поддерживаемый диапазон DATETIME: С «1000-01-01 00:00:00» по «9999-12-31 23:59:59»