MySQL Data Types DATE, DATETIME, TIMESTAMP, YEAR, and TIME
Example
The DATE datatype comprises the date but no time component. Its format is 'YYYY-MM-DD' with a range of '1000-01-01' to '9999-12-31'.
The DATETIME type includes the time with a format of 'YYYY-MM-DD HH:MM:SS'. It has a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
The TIMESTAMP type is an integer type comprising date and time with an effective range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
The YEAR type represents a year and holds a range from 1901 to 2155.
The TIME type represents a time with a format of 'HH:MM:SS' and holds a range from '-838:59:59' to '838:59:59'.
Storage Requirements:
|-----------|--------------------|----------------------------------------|
| Data Type | Before MySQL 5.6.4 | as of MySQL 5.6.4 |
|-----------|--------------------|----------------------------------------|
| YEAR | 1 byte | 1 byte |
| DATE | 3 bytes | 3 bytes |
| TIME | 3 bytes | 3 bytes + fractional seconds storage |
| DATETIME | 8 bytes | 5 bytes + fractional seconds storage |
| TIMESTAMP | 4 bytes | 4 bytes + fractional seconds storage |
|-----------|--------------------|----------------------------------------|
Fractional Seconds (as of Version 5.6.4):
|------------------------------|------------------|
| Fractional Seconds Precision | Storage Required |
|------------------------------|------------------|
| 0 | 0 bytes |
| 1,2 | 1 byte |
| 3,4 | 2 byte |
| 5,6 | 3 byte |
|------------------------------|------------------|
See the MySQL Manual Pages DATE, DATETIME, and TIMESTAMP Types, Data Type Storage Requirements, and Fractional Seconds in Time Values.
