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.

Answer 2

Could you do something like this:

public function up()
{
    Schema::create('mytable', function(Blueprint $table)
    {
        $table->increments('id');
        // columns
        $table->timestamps();
    });

    DB::statement('ALTER mytable ADD year YEAR' );

}

Answer 3

I think this should work:

Schema::table('tablea_name', function(Blueprint $table)
{
    DB::statement('ALTER TABLE table_name ADD year YEAR(4);');
});

Portable solution would be if Laravel provides any way to access this method (in /vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php) in upcoming versions:

/**
 * Add a new column to the blueprint.
 *
 * @param  string  $type
 * @param  string  $name
 * @param  array   $parameters
 * @return \Illuminate\Support\Fluent
 */
protected function addColumn($type, $name, array $parameters = array())
{
    $attributes = array_merge(compact('type', 'name'), $parameters);

    $this->columns[] = $column = new Fluent($attributes);

    return $column;
}

Answer 4

As at Laravel 5.8, you can do this:

$table->year('birth_year');