Click here to Skip to main content
13,709,065 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

15.9K views
3 bookmarked
Posted 16 Nov 2017
Licenced CPOL

MySQL: DATETIME vs TIMESTAMP

, 16 Nov 2017
Rate this:
Please Sign up or sign in to vote.
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 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Suvendu Shekhar Giri
Software Developer
India India
No Biography provided

Comments and Discussions

 
PraiseMySQL version Pin
INDtanay21-Nov-17 7:01
memberINDtanay21-Nov-17 7:01 
GeneralRe: MySQL version Pin
Suvendu Shekhar Giri21-Nov-17 17:47
mvpSuvendu Shekhar Giri21-Nov-17 17:47 
GeneralMy vote of 2: Portions are incorrect or unclear Pin
Jochen Arndt16-Nov-17 21:46
mvpJochen Arndt16-Nov-17 21:46 
GeneralRe: My vote of 2: Portions are incorrect or unclear Pin
Suvendu Shekhar Giri21-Nov-17 17:49
mvpSuvendu Shekhar Giri21-Nov-17 17:49 
GeneralRe: My vote of 2: Portions are incorrect or unclear Pin
Prava-MFS21-Nov-17 22:29
professionalPrava-MFS21-Nov-17 22:29 
GeneralRe: My vote of 2: Portions are incorrect or unclear Pin
Jochen Arndt21-Nov-17 23:02
mvpJochen Arndt21-Nov-17 23:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.180920.1 | Last Updated 16 Nov 2017
Article Copyright 2017 by Suvendu Shekhar Giri
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid