When I upgraded my website to use Laravel I already had a database with blog posts. As part of the schema one of the columns was called date_time and was of type int(11) and contained plain unix timestamps. To speed up migrating my code base to Laravel I kept this column and set my articles model to not use Laravel’s timestamps.

I’ve now got round to updating my database to use said timestamps. Here’s what I did. First I created the relavent new columns:

[sql]
> ALTER TABLE `articles` ADD `created_at` TIMESTAMP DEFAULT 0;
> ALTER TABLE `articles` ADD `updated_at` TIMESTAMP;
> ALTER TABLE `articles` ADD `deleted_at` TIMESTAMP NULL;

I needed to add DEFAULT 0 when adding the created_at column to stop MariaDB setting the default value to CURRENT_TIMESTAMP as well as adding an extra rule of updating the column value on a row update.

Then I needed to populate the column values based on the soon to be redundant date_time column. I took advantage of the fact the values were timestamps:

[sql]
> UPDATE `articles` SET `created_at` = FROM_UNIXTIME(`date_time`);
> UPDATE `articles` SET `updated_at` = FROM_UNIXTIME(`date_time`);

Now I can delete the old date_time column:

[sql]
> ALTER TABLE `articles` DROP `date_time`;

Next I had to get Eloquent to work properly. I wantet /blog to show all articles, I wanted /blog/{year} to show all articles from a given year, and I wanted /blog/{year}/{month} to show all articles from a given month. My routes.php handled this as Route::get('blog/{year?}/{month?}', 'ArticlesConrtoller@showArticles');. I then defined a query scope so I could do $articles = Articles::dates($year, $month)->get(). Clearly these variables could be null so my scope was defined as follows:

[php]
public function scopeDate($query, $year = null, $month = null)
{
    if ($year == null) {
        return $query;
    }
    $time = $year;
    if ($month !== null) {
        $time .= '-' . $month;
    }
    $time .= '%';
    return $query->where('updated_at', 'like', $time);
}

The logic takes advantage of the fact I know that $year can’t be null whilst simultaneously $month be not-null. i.e. when there is no year just return an unmodified query.

And now my blog posts are handled by Laravel properly.