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
- Comparison Chart
- What is DATETIME?
- What is TIMESTAMP?
- Date and Time Interpretation
- Key Differences
- Similarities Between DATETIME and TIMESTAMP
|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
|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:
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:
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:
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:
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’.
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:
- 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.
- 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:
- Year value ranging from 00 to 69 is interpreted as 2000-2069.
- Year value ranging from 70 to 99 is interpreted as 1970-1999.
Key Differences Between DATETIME and TIMESTAMP
- With the DATETIME data type, we can store the dates from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. On the other hand, with the TIMESTAMP data type, we can store dates from 1970-01-01 00:00:01 to 2038-01-19 08:44:07. So, the range of DATETIME is larger than the range of TIMESTAMP.
- Before the MySQL 5.6.4 version, the DATETIME used to occupy 8 bytes with an additional 0 to 3 bytes for fractional seconds. Currently, the MySQL 5.6.4 version occupies only 5 bytes for the DATETIME data type with an addition of 0 to 3 bytes for storage of fractional seconds. However, the TIMESTAMP data type occupies 4 bytes with an additional 0 to 3 bytes for storage of fractional seconds.
- While storing, MySQL converts the values of the TIMESTAMP data type from the current time zone to UTC (Coordinated Universal Time), and while retrieving, it converts it back to the current time zone. However, no conversion takes place in the case of DATETIME.
Similarities Between DATETIME and TIMESTAMP
- DATETIME and TIMESTAMP data types are used for values that include data and time.
- The format they use to express their values is YYYY-MM-DD HH:MM:SS.
- Automatic initialization and updation to the current date and time happen in both.
- Both have additional storage for fractional seconds with the format YYY-MM-DD HH:MM:SS.[fraction].
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.