I was looking to implement a year column and while researching I stumbled on the YEAR data type which sounded just right by its name, I assumed that it would just be something like an integer that can maybe hold only 4 digits, maybe more if negative?
But then I noticed while actually trying it out that some years I was inputting randomly by hand never went through giving an out of range error, so I went to look at the full details and, sure enough, it’s limited to years between 1901 and 2155, just 2155!
In terms of life of an application 2155 is just around the corner, well not that any software has ever lived that long, but you get what I mean in the sense that we want our programs to be as little affected by time within what’s reasonable given space constraints.
So what will they do when they get close enough to that year, because you don’t even have to be in that year to need it accessible, there could be references that point to the future, maybe for planning of some thing or user selected dates and whatnot; will they change the underlying definition of it as time passes so it’s always shifted forward? If that’s the approach they’ll take, will they just tell everyone who’s using this type that their older dates will just not be supported anymore and they need to migrate to a different type? YEAR-OLD? Then YEAR-OLDER? Then YEAR-OLDER-BUT-LIKE-ACTUALLY? Or, that if they plan to stay in business, they should move to SMALLINT?
Or will they take the opposite approach and put out a new YEAR datatype every time the 256 range is expired like YEAR-NEW, YEAR-NEW-1, YEAR-FINAL, YEAR-JK-GUYS-THE-WORLD-HASNT-COLLAPSED, etc.?
So I wonder, what’s the point of this data type? It’s just so incredibly restricted that I don’t see even a hypothetical use.
There exist other questions like this (example) but I think they all don’t address this point: has anyone from MariaDB or MySQL or an SQL committee (I don’t know if that’s a thing) wrote up some document that describes the plan for how this datatype will evolve as time passes? An RFC or anything like that?
I expect it won’t
The year datatype is a 1 byte integer, but the engine adds/subtracts 1900 to the value under the hood and has special handling for zero.
If you need to store more than 255 years range, you can use a 2 byte integer, which doesn’t need that special handling under the hood, because with 2 bytes you can store 65000+ years
Yes 2 bytes is absolutely fine for me in fact (waiting for this comment to age like milk in my cryo pod), but then if YEAR will just stay the same forever, will it become a relic of the past? If so, why YEAR in the first place, who would actually make use of it?
will it become a relic of the past?
Probably
why YEAR in the first place, who would actually make use of it?
Accounting systems in the 90s that needed to squeeze out every drop of performance imaginable
90’s? I assumed it was from the 80s or earlier
Right? it screams wayyyy pre-y2k but MySQL was only release in 95
💀
That does make sense! I like the point about older systems, I didn’t even stop to think about how much storge space has exploded in such a short amount of time and how it started from incredibly small capacities at very high prices that could have been hard to justify for any company that realistically just needed to keep some records
To answer the part of your question I think is most fun, there is a standard for SQL. There are many dialects of SQL, but you’ll often hear of “ANSI SQL.” The latest version is SQL:2023.
Looking at the MySQL manual entry for the YEAR type, I think we can conclude two things:
-
The developers consider the possibility of deprecating and removing support for time data type features;
-
They use “reasonable defaults” for conversions of 2 digit years, based on the current year.
The good news is it sounds like this issue is being taken into account. I’m sure the conversion window will be adjusted in future version and the data type may be changed or deprecated altogether. I wouldn’t be surprised if they added a YEAR2 though. T-SQL has a datetime2, after all.
That’s really interesting!
The good news is it sounds like this issue is being taken into account.
Is there a part in that page that says so? I wasn’t able to find it
I wouldn’t be surprised if they added a YEAR2 though. T-SQL has a datetime2, after all.
Ok I wasn’t expecting that, it sounds like a meme, but it’s actually real lol
I’m inferring based on the deprecation of YEAR(4) and the conversion time window that obviously needs to be updated at some point.
Could be, it looks pretty unknown for now though
-
