Why does the MySQL year type limit the range of allowable years between 1901 and 2155?
+3
A:
Presumably, if you are storing more recent dates, you will never need more than 256 possible year values, which fits exactly into one byte. This saves you significant amounts of space: rather than using multiple bytes to store the integer 1901, 1900 years of which can be considered redundant in many cases, MySQL internally treats it as just the number 1, and displays it at 1901 for your benefit.
If you need more years, use an INT-based type.
Matchu
2010-03-22 19:15:34
+1
A:
In order to fit the value into a single byte. The choice of 1901-2155 is arbitrary, but the fact that it supports 1-byte worth of values is not.
Ike Walker
2010-03-22 19:16:02
A:
The year type is a one-byte type. One byte = 256 values. Probably done this way because most dates will be in that range so it saves bytes...
You can use a string for older dates.
Coronatus
2010-03-22 19:16:16
No thanks on using strings. If the DATE type ('1000-01-01' to '9999-12-31') doesn't cover you, then suck it up and use 3 integer fields, or else you're using tons of unnecessary storage. It's the difference between 4 bytes and 10 bytes.
Matchu
2010-03-22 19:19:03
Never ever use strings for storing dates! Use the appropriate type -unless- there's no other way, using the proper types gets you all kind of benefits: no string formatting issues, ability to extract parts, proper sorting, calculate differences between dates and so on, and so on.
Martijn Tonies
2010-03-23 08:44:48