Search code examples
mysqldatetimedata-storage

Why date time takes up 8bytes in data storage, such as MySQL, c#

As my title, why date time takes 8 bytes, date takes 3, time takes3？

Solution

  • Actual storage requirements for date/time fields depend on two things:

    • Representation. This is typically number of <time unit> since <epoch> and a range of supported dates, i.e. '1000-01-01' to '9999-12-31' for MySQL.
    • Storage structure limitations, i.e. we probably need to use multiples of bytes to store data (i.e. we can't easily store something using i.e. 2.36 bytes...)

    Regarding MySQL date, the range is '1000-01-01' to '9999-12-31', or 9000 years (give or take) and we need a granularity of a day. So we need to store the number of days since the epoch (1000-01-01) up to 9000*365 = 3285000. Since the number is 0-3285000, we can't use 2 bytes (max 65536) and we use 3 (max 16777216). Yes, this is more than we need but we can't have fractions of a byte.

    Regarding MySQL time, the range is '-838:59:59' to '838:59:59' (it can be used to represent duration/interval, not just the time of day) which is roughly 6040800 seconds. Again we need 3 bytes to accommodate that.

    Regarding MySQL datetime, the range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. There is a huge difference in the precision here, it can now hold 9000 years with a granularity of 0.000001 of a second! That's why we need 8 bytes to store datetime.