Difference Between MySQL DATETIME And TIMESTAMP DataTypes

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; let's check those things out with the help of a small example.

Similarities between DATETIME & TIMESTAMP

1. Both store the data in "YYYY-MM-DD HH:MM:SS" format.
2. Both include a date as well as a 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 up to 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 the 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. TIMESTAMPdiffers with current time zone settings while DATETIME remains constant.
6. TIMESTAMP data can be indexed while the DATETIME data cannot.
7. Queries withDATETIME will not be cached but queries with TIMESTAMP will be cached.

Example (DATETIME)

My system time zone is IST, so by default, MySQL uses IST time zone.

mysql>CREATE TABLE `employee`
             (
                          `entry_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON
             UPDATE CURRENT_TIMESTAMP
             )
             engine=innodb DEFAULT charset=latin1 mysqlINSERT INTO `employee`
                  (
                              `entry_time`
                  )
                  VALUES
                  (
                              CURRENT_TIMESTAMP
                  );mysql>SELECT *
      FROM   `employee`;

OUTPUT

entry_time

___________________

2017-11-17 07:38:07

Now, let's change the system time zone 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 time zone.

mysql>CREATE TABLE `employee`
             (
                          `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
             UPDATE CURRENT_TIMESTAMP
             )
             engine=innodb DEFAULT charset=latin1 mysqlINSERT 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 the system time zone 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 is subject to change to the set time zone, i.e., EST which is -5hrs from UTC time.

Even though both datatypes look similar, these are 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