Отличия типов datetime и timestamp в MySQL

Отличия типов datetime и timestamp в MySQL

На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных 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 |
+---------------------+

Источник

By sysadmin on 24.11.2009 | MySQL
Метки: , , ,