На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных DATETIME и TIMESTAMP в MySQLе, а так же как хранить дату и время, если необходимо учитывать разные часовые пояса для разных пользователей веб-приложения. Поэтому хочу дать ниже разъяснения с пояснениями.
DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL. Даю пример:
mysql> CREATE TABLE `dt1` ( col datetime NOT NULL ); mysql> SET @@SESSION.time_zone='+00:00'; mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2009-06-04 18:13:56 | +---------------------+ mysql> INSERT INTO dt1 VALUES(now()); mysql> SET @@SESSION.time_zone='+01:00'; mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2009-06-04 19:14:20 | +---------------------+ mysql> INSERT INTO dt1 VALUES(now()); mysql> SET @@SESSION.time_zone='+00:00'; mysql> SELECT * FROM dt1; +---------------------+ | col | +---------------------+ | 2009-06-04 18:14:10 | | 2009-06-04 19:14:27 | +---------------------+
TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу. Пример:
mysql> CREATE TABLE tm1 (col TIMESTAMP NOT NULL); mysql> SET @@SESSION.time_zone = '+00:00'; mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2009-06-04 18:24:54 | +---------------------+ mysql> INSERT INTO tm1 VALUES(now()); mysql> SELECT * FROM tm1; +---------------------+ | col | +---------------------+ | 2009-06-04 18:25:08 | +---------------------+ mysql> SET @@SESSION.time_zone = '+01:00'; mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2009-06-04 19:25:21 | +---------------------+ mysql> INSERT INTO tm1 VALUES(now()); mysql> SELECT * FROM tm1; +---------------------+ | col | +---------------------+ | 2009-06-04 19:25:08 | | 2009-06-04 19:25:26 | +---------------------+ mysql> SET @@SESSION.time_zone = '+00:00'; mysql> SELECT * FROM tm1; +---------------------+ | col | +---------------------+ | 2009-06-04 18:25:08 | | 2009-06-04 18:25:26 | +---------------------+
Ещё одно отличие! TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().
mysql> INSERT INTO dt1 VALUES(NULL); ERROR 1048 (23000): COLUMN 'col' cannot be NULL mysql> INSERT INTO tm1 VALUES(NULL); Query OK, 1 ROW affected (0.00 sec) mysql> SELECT * FROM tm1; +---------------------+ | col | +---------------------+ | 2009-06-04 18:25:08 | | 2009-06-04 18:25:26 | | 2009-06-04 18:32:50 | +---------------------+
Дополнение. Для тех, кого смущает использование функции NOW().
mysql> SET @@SESSION.time_zone = '+00:00'; mysql> INSERT INTO dt1 VALUES('2009-06-04 22:00:00'); mysql> SET @@SESSION.time_zone = '+01:00'; mysql> SELECT * FROM dt1; +---------------------+ | col | +---------------------+ | 2009-06-04 22:00:00 | +---------------------+ mysql> SET @@SESSION.time_zone = '+00:00'; mysql> INSERT INTO tm1 VALUES('2009-06-04 22:00:00'); mysql> SET @@SESSION.time_zone = '+01:00'; mysql> SELECT * FROM tm1; +---------------------+ | col | +---------------------+ | 2009-06-04 23:00:00 | +---------------------+