Why MySql DATETIME takes 8 Bytes instead of 6 Bytes?

Question

MySql DATE is occupying 3 Bytes and TIME is occupying 3 Bytes but DATETIME is taking 8 Bytes. I assume like it should be 6 Bytes (3+3) instead of 8 Bytes. What is the logic behind this and why the extra two bytes are used?

Answer 1

According to MySQL internals documentation, it's needed because of specific storage requirements:

+-----------+------------------------+---------------------+
|   YEAR    | 1 byte, little endian  |      Unchanged      |
+-----------+------------------------+---------------------+
|   DATE    | 3 bytes, little endian |      Unchanged      |
+-----------+------------------------+---------------------+
|           |                        | 3 bytes +           |
|   TIME    | 3 bytes, little endian | fractional-seconds  |
|           |                        | storage, big endian |
+-----------+------------------------+---------------------+
|           |                        | 4 bytes +           |
| TIMESTAMP | 4 bytes, little endian | fractional-seconds  |
|           |                        | storage, big endian |
+-----------+------------------------+---------------------+
|           |                        | 5 bytes +           |
| DATETIME  | 8 bytes, little endian | fractional-seconds  |
|           |                        | storage, big endian |
+-----------+------------------------+---------------------+

In particular, DATETIME has 8 bytes:

• 4 bytes: integer for date represented as YYYY×10000 + MM×100 + DD
• 4 bytes: integer for time represented as HH×10000 + MM×100 + SS

So it's important to realize, that output representation and storage representation are two very different things. As you can see, structure for datetime is two integers with some internal calculations for both parts - date and time.