I need to store a date that could sometimes be a year only, sometimes a year and month only and sometimes a complete year, month day.
I've considered date but this won't accept just a year or just a year and a month. I've also considered multiple columns for each part of a date - but this get's messy pretty quickly when ive got to store several partial dates for different things.
There's also the possibility of a using a varchar, but this does not seem right.
One solution could be to use a single column to store the partial date as a string in a standardized format, such as "YYYY-MM-DD" or "YYYY-MM" for year and month only. This way, you can easily parse the string to get the relevant parts of the date when needed. For example, if you have a partial date of "2010-05", you can extract the year as 2010 and the month as 05.
Here's an example of how you could store and retrieve a partial date using a string column in Laravel's Eloquent ORM:
// Migration to add partial_date column to table
Schema::table('my_table', function (Blueprint $table) {
$table->string('partial_date')->nullable();
});
// Model with accessor to retrieve year and month from partial_date
class MyModel extends Model
{
protected $fillable = ['partial_date'];
public function getYearAttribute()
{
return substr($this->partial_date, 0, 4);
}
public function getMonthAttribute()
{
return substr($this->partial_date, 5, 2);
}
}
// Example usage
$model = new MyModel(['partial_date' => '2010-05']);
$model->save();
echo $model->year; // Output: 2010
echo $model->month; // Output: 05
@Snapey thanks, i would need to sort by the column, and i would need to know if its a partial date so 2020-01-01 would not be ideal if this was only a year date.
@Snapey i think it would probably just be sortable by year, with the month and day (if given) giving the user additional information on a listings page.
