Clever Geek Handbook

What is the type of column to store the year field in a table with year data rows

My Laravel web application uses a schema constructor 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?

+3


source to share


3 answers


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

smallint

is a good choice for representing years and it is ANSI SQL so will work on most databases. It will last until 32767.



Some databases support create domain

which basically allows you to create your own types. You can create a domain year

for other databases. However, I prefer the simplicity of smallint throughout the year.

+5


source


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' );

}
+1


source


I think this should work:

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

A portable solution would be if Laravel provides any way to access this method (in /vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php

) in the following 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;
}
+1


source






More articles:


All Articles