Datetime or timestamp

The other day I was faced with the fact that many developers do not know what is the difference between the DATETIME and TIMESTAMP data types in MySQL, and also how to store the date and time if you need to take into account different time zones for different users of the web application. Therefore, I want to give below explanations with explanations.

DATETIME
Stores the time as an integer of the form YYYYMMDDHHMMSS, using 8 bytes for this. This time does not depend on the time zone . It is always displayed when sampling exactly as it was saved, no matter what time zone is set in MySQL. I give an example:

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
Stores a 4-byte integer equal to the number of seconds elapsed since midnight on January 1, 1970 at the Greenwich Mean Time (i.e., the zero time zone, the starting point of time zones). When received from the database is displayed taking into account the time zone. The time zone can be set in the operating system, MySQL global settings, or in a specific session. Remember that the number of seconds is always UTC (universal coordinated time, solar time on the Greenwich meridian), and not the local time zone. Example:
')
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 |
+---------------------+


Another difference! TIMESTAMP is NOT NULL by default, and its default value is 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 |
+---------------------+


Addition. For those who are confused by the use of the function 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 |
+---------------------+