laravel date and time column concat to datetime column-CodePudding

An error occurred during database design and the date time was handled in a separate field, which I would like to merge using migration. Is there some simple way to move the data in the date and time fields to a dateTime field without losing the existing data?

Current table:

id | publish_date | publish_time
--------------------------------
1  | 2021-01-01   | 10:25:00

that's what i want it to look like, whitout data loss:

id | publish_date_time
----------------------
1  | 2021-01-01 10:25:00

CodePudding user response：

A migration could be used to do this:

You can create one using php artisan make:migration MergeDateColumns and then do:

class MergeDateColumns extends Migration {
    public function up() {
       Schema::table('your table name', function (Blueprint $table) {
           $table->dateTime('publish_date_time');
       });
       DB::table('your table name')
           ->update([ 'publish_date_time' => DB::raw("CONCAT(publish_date,' ', publish_time)") ]);
       Schema::table('your table name', function (Blueprint $table) {
           $table->dropColumn('publish_date');
           $table->dropColumn('publish_time');
       });
    } 
    public function down() {
       Schema::table('your table name', function (Blueprint $table) {
           $table->date('publish_date');
           $table->time('publish_time');
       });
       DB::table('your table name')
           ->update([ 
              'publish_date' => DB::raw("DATE(publish_date_time)"),
              'publish_time' => DB::raw("TIME(publish_date_time)")
            ]);
       Schema::table('your table name', function (Blueprint $table) {
           $table->dropColumn('publish_date_time');
           $table->dropColumn('publish_time');
       });
    } 

}

This merges the columns when you migrate and will split them again when you roll back.

To be safe I would take a backup of the data before running this on production but I don't think it would cause problems.

CodePudding user response：

You can simply do this by the following code (in a method) :

$posts = Post::query()->selectRaw("id, concat(publish_date,' ', publish_time) as publish_date_time")->get();
    foreach ($posts as $post) {
        Post::query()->where("id", $post->id)->update([
            'publish_date_time' => $post->publish_date_time
        ]);
    }

make sure that you already have the "publish_date_time" column using a migration

CodePudding user response：

I think you are looking for this? use can use DB::raw for concatenation.

$articals = Artical::select("*", DB::raw("CONCAT(publish_date,' ', publish_time) as publish_date_time"))->get();

So, as a result you will get

2021-01-18 04:10:35