keithzg keithzg - 1 year ago 102
SQL Question

Why is MySQL's maximum time limit 838:59:59?

I've run into the limit myself, but despite lots of chatter online, I've never seen an explanation for why the upper and lower limit for the TIME data type is what it is. The official reference at says

TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

But I'm wondering not why the hours part is allowed to be "so large", but why it's cut off where it is. There doesn't seem to be any significance to that many hours in regards to days, or if I try to imagine possible cutoffs for how many seconds could be stored as an integer. So why the range?

Answer Source

DATETIME is stored based on a base of 10, see Date and Time Data Type Representation:

DATETIME: Eight bytes: A four-byte integer for date packed as YYYY×10000 + MM×100 + DD and a four-byte integer for time packed as HH×10000 + MM×100 + SS

For convinience and some other reasons, the (old) time format was encoded in the same way, using 3 bytes:

Hours * 10000 + Minutes * 100 + Seconds

This means:

3 bytes = 2^24 = 16.777.216
with sign: 2^23 = 8.388.608

Using the encoding, this represents the magical 838 hours. And max. 8608 seconds for the minutes and seconds (without overflow), which results in the largest valid time 838:59:59. One nice thing about this is that the integer representation of that time, 8385959, is easily readable to a human. But this encoding of course leaves gaps, invalid (unused) integer values (like 8309999).

As of MySQL 5.6.4, time format changed its encoding to

  1 bit sign    (1= non-negative, 0= negative)
  1 bit unused  (reserved for future extensions)
 10 bits hour   (0-838)
  6 bits minute (0-59) 
  6 bits second (0-59) 
  24 bits = 3 bytes

Even though it could now store more hours, for compatibility it still just allows 838 hours.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download