How is DATETIME stored in MySQL?

MySQL uses a brilliant technique to store and retrieve dates efficiently. This method allows for the storage of a compact version of a date that requires fewer bits.

It uses 8 bytes to store Date and Time, let's break it down.

Total: 64 bits = 8 bytes

Let's focus on the Date part here,

Question: How does it hold the date in a compact format & Why multiply with 13?

The reason for choosing 13 as the multiplier is that it allows for a convenient representation of leap years. When a year is divisible by 4, it is considered a leap year and has 366 days instead of the usual 365. In the MySQL DateTime format, leap years are represented by adding 1 to the month value.

An Example

Let's say we want to store the date (YYYY - MM): 2023-12

as per the calculation = 2023 × 13 = 26299

then (+) the month = 26299 + 12 = 26311

So, the compact form of 2023-12 is 26311

How do we extract the year and month from the compact form?

for YEAR = 26311 / 13 = 2023

for MONTH = 26311 % 13 = 12

That's how it works. for the Day there is another field of which is of 5 bits. 

Official MySQL GitHub: Check GitHub Code

Thanks!