Which column type for storing the year field in a table with rows of yearly data

Question

My Laravel web app uses the schema builder for database portability, so the MySQL-specific YEAR column type is not available.

I want to be able to sort and select rows of data by year (including BETWEEN). What's the best portable column type for storing year values?

Answer 1

What's the best portable column type for storing year values?

smallint is a good choice to represent years, and it's ANSI SQL, so will work in most databases. It will last until the year 32767.

Some databases support create domain which basically lets you create your own types. You could create a year domain for other databases. I prefer the simplicity of smallint for year though.