Differences Between DATETIME and TIMESTAMP

DATETIME and TIMESTAMP are the data types in MySQL. Both of them can store the values that include data and time part. The significant difference between these two data types is the range of values they accept. The DATETIME data type supports a larger range as compared to TIMESTAMP.

As DATETIME supports a larger range, the size of this data type is even bigger than that of TIMESTAMP. In this section, we will explore these data types in detail. We will also identify the maximum differences between these two similar data types.

Content: DATETIME Vs TIMESTAMP

1. Comparison Chart
2. What is DATETIME?
3. What is TIMESTAMP?
4. Date and Time Interpretation
5. Key Differences
6. Similarities Between DATETIME and TIMESTAMP
7. Conclusion

Comparison Chart

Basis of Comparison	DATETIME	TIMESTAMP
Range	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’	‘1970-01-01 00:00:01 UTC’ to ‘2038-01-19 03:14:07’ UTC
Size	8 bytes in prior Version of MySQL 5 bytes in current MySQL 5.6.4 version	4 bytes
Conversion	There is no conversion	Converts current time to UTC and vice-versa

What is DATETIME?

DATETIME is a data type that holds the value that specifies both date and time. The precision of the value ranges from a year to a fraction of a second. The format it uses to retrieve and display the value is:

YYYY-MM-DD HH:MM:SS

In MySQL 5.6.4, the DATETIME data type reserves 5 bytes to store the value. However, before MySQL 5.6.4, it would reserve 8 bytes. In addition, it may require an extra 0 to 3 extra bytes to store fractional seconds, which is 6 digits. So, the format with additional data becomes:

YYY-MM-DD HH:MM:SS.[fraction]

In the DATETIME data type, the range of date that we can go up to is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. If the value includes the fractional part, then the value of DATETIME can range from ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. Thus, it supports a huge range of dates and times.

What is TIMESTAMP?

TIMESTAMP is also a data type with a value specifying date and time. The format it uses to retrieve and display the value is:

YYYY-MM-DD HH:MM:SS

TIMESTAMP data type reserves 4 bytes to store the value, adding 0 to 3 bytes for fractional seconds. So, the format of the TIMESTAMP value with additional data becomes:

YYY-MM-DD HH:MM:SS.[fraction]

In the TIMESTAMP data type, the range of date that we can go up to is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. If the value includes the fractional part, then the value of TIMESTAMP can range from ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’.

While storing the TIMESTAMP value, MySQL converts the value from the current time zone to coordinated universal time (UTC). However, MySQL converts it back to the current time zone during retrieval.

Date and Time Value Interpretation in MySQL

• Although MySQL permits a relaxed format for the date and time values specified as a string. Such as, it accepts any punctuation character used as a delimiter to separate the date or time part. But sometimes, the relaxed format can create problems such as:
  1. The value 11:12:13 might appear as time due to the delimiter ‘:’. If the value is used in the date context, MySQL will interpret this value as 2011-12-13. If the value 10:45:46 is used in the date context, MySQL will interpret this as 0000-00-00. Because neither 45 is a valid month nor 46 is a valid day.
  2. But in the case of fractional time, MySQL only accepts decimals as a delimiter between date and time part and fraction seconds.
• MySQL not only requires that the month and day must be in the range 1 to 12 and 1 to 31, respectively. But it also requires that the date must be valid. Like, 2022-04-31 is an invalid date, so MySQL will convert it to 0000-00-00, generating a warning.
• TIMESTAMP values with 0 in the day or month column or with invalid dates are not acceptable in MySQL.
• Dates with two digits in the year column are ambiguous as two digits do not address the century. In this case, MySQL interprets values as:
  1. Year value ranging from 00 to 69 is interpreted as 2000-2069.
  2. Year value ranging from 70 to 99 is interpreted as 1970-1999.

Conclusion

So, these are the differences between the DATETIME and TIMESTAMP data types. If you are creating an app that holds the dates before or after the current century, then you must use the DATETIME data type as it supports dates from the year 1000 up to year 9999.

But if you are running out of space and your app deals with the date ranging from 1970 to 2038, then you can use the TIMESTAMP data type, as it occupies only 4 bytes.