Post
Ask Question

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 the "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 the current date time as per the constraint.
  5. Both can have fractional seconds parts 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 of data storage.
  4. In MySQL5+, the TIMESTAMP value converts from the 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 with DATETIME will not be cached, but queries with TIMESTAMP will be cached.

Example

(DATETIME)

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

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

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

SELECT *
FROM `employee`;

Output

output

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

-- Set the session time zone to '-05:00'
SET @@session.time_zone = '-05:00';

-- Insert a new record into the `employee` table with the current timestamp in the `entry_time` column
INSERT INTO `employee` (`entry_time`) VALUES (CURRENT_TIMESTAMP);

-- Retrieve all records from the `employee` table and display the contents
SELECT * FROM `employee`;

Output

output2

Example

(TIMESTAMP)

The result is the same even though we changed the time zone.

CREATE TABLE `employee`
(
    `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

SELECT *
FROM `employee`;

Output

output3

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

output4

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 data types. Please share your feedback if you find this tip helpful for you.

Reference


Similar Articles