MySQL: Datetime Versus Timestamp Data Types

The temporal data types in MySQL can be confusing. Hopefully, this example and discussion will help to explain the differences in the timestamp and datetime data types.

From the MySQL reference:

The DATETIME type is used for values that contain both date and time parts. 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 is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

A major difference between these two data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime data type values are unchanged in relation to time zone.

This example is a good exercise in demonstrating the difference between these two data types.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    |  Value              |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+
2 rows in set (0.00 sec)

You can see our current time zone information. Under this environment, let us create a table with the two data types and populate it with the same temporal information.

create table datedemo
(
 mydatetime datetime,
 mytimestamp timestamp
);

Query OK, 0 rows affected (0.05 sec)

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

Query OK, 1 row affected (0.02 sec)

select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

At this point the datetime and timestamp data types have remained the exact same values. Let us change the time zone see the results.

SET TIME_ZONE = "america/new_york";

Query OK, 0 rows affected (0.00 sec)

 select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The above example shows how the TIMESTAMP date type changed the values after changing the time-zone to ‘america/new_work’ where DATETIME is unchanged.