MySQL: DATETIME vs TIMESTAMP

Introduction

A simple question that may come to one's mind while working with MySQL is "Whether to assign DATETIME or TIMESTAMP datatype for a column as both seem to store the same data?". Even though they store the same data, they differ in some ways and let's check those things out with the help of a small example.

Similarities between DATETIME & TIMESTAMP

1. Both store data in "YYYY-MM-DD HH:MM:SS" format.
2. Both include date as well as time part.
3. Automatic initialization can happen for both.
4. Both change the data while updating the record with current data time as per the constraint.
5. Both can have fractional seconds part upto 6 digit microsecond precision.

Difference between DATETIME & TIMESTAMP

1. Supported range for DATETIME is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' while for TIMESTAMP, it is '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
2. Prior to MySQL 5.6.4, TIMESTAMP requires 4 bytes (+3 bytes for fractional seconds) to store data while DATETIME requires 8 bytes (+3 bytes for fractional seconds).
3. As of MySQL 5.6.4, DATETIME requires 5 bytes + 3 additional bytes for fractional seconds data storing.
4. In MySQL5+, TIMESTAMP value converts from current time to UTC and vice-versa while DATETIME does not do any conversion.
5. TIMESTAMP differs with current timezone settings while DATETIME remains constant.
6. TIMESTAMP data can be indexed while DATETIME data can not.
7. Queries with DATETIME will not be cached but queries with TIMESTAMP will be cached.

Example (DATETIME)

My system timezone is IST, so by default MySQL use IST timezone.

mysql> CREATE TABLE `employee` (
 `entry_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> INSERT INTO `employee` (`entry_time`) VALUES (CURRENT_TIMESTAMP);

mysql> SELECT * FROM `employee`;

OUTPUT

entry_time 
___________________
2017-11-17 07:38:07

Now let's change system timezone from IST to EST, i.e., UTC - 05:00 during cold months of Daylight Saving Time.

mysql> SET @@session.time_zone = '-05:00';
mysql> INSERT INTO `employee` (`entry_time`) VALUES (CURRENT_TIMESTAMP);
mysql> SELECT * FROM `employee`;

OUTPUT

entry_time
____________________
2017-11-17 07:38:07
2017-11-17 07:45:01

Example (TIMESTAMP)

The result is the same even though we changed the timezone.

mysql> CREATE TABLE `employee` (
 `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> INSERT INTO `employee` (`entry_time`) VALUES (CURRENT_TIMESTAMP);
mysql> SELECT * FROM `employee`;

OUTPUT

entry_time
___________________
2017-11-17 07:49:33

mysql> SET @@session.time_zone = '-05:00'; SELECT * FROM `employee`;

Now let's change system timezone from IST to EST, i.e., UTC - 05:00 during cold months of Daylight Saving Time.

OUTPUT

entry_time 
____________________
2017-11-16 12:19:3

Conclusion

The above result changed to the set timezone, i.e., EST which is -5hrs from UTC time.

Even though both datatypes look similar, but it is way different than what we might have thought of. Hope this small tip will help someone in some way for differentiating these 2 confusing datatypes. Please share your feedback if you find this tip helpful for you.

Reference

https://dev.mysql.com/doc/refman/5.7/en/datetime.html