Why does MySql DATETIME occupy 8 bytes instead of 6 bytes?

Question

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

Answer 1

According to MySQL internal documentation, this is necessary due to special 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: an integer for the date represented as YYYY×10000 + MM×100 + DD
• 4 bytes: an integer in time represented as HH×10000 + MM×100 + SS

Therefore, it is important to understand that presentation and storage are two different things. As you can see, the structure for datetime is two integers with some internal calculations for both parts - date and time.