0

I've got a problem, some donkey in my company create a database with MySql, we got a column for the date and he set it as a varchar.
We cant build an other database because the it's at least here since 10 yo. Now I have some treatment to do with the data and especially with the date (that I need to be a datetime). Of course the date are in the format 'dd/mm/yyyy h:i' and when I'm trying to convert the varchar into a datetime I've got this error:
#1292 - Incorrect datetime value: '24/08/2020 22:05' for column 'column_name' at row 1
I'm working with Laravel and Mysql

Anyone know how can I get out of this ?

Romain
  • 3
  • 4
  • you can use Carbon, docs https://carbon.nesbot.com/docs/ – Alzafan Christian Sep 03 '20 at 09:38
  • Surely the only way is to run through the database and convert all of those into correctly-formatted date/times, then try to change the column type. – droopsnoot Sep 03 '20 at 09:40
  • 1
    `24/08/2020 22:05` it should be `2020-08-24 22:05:01` – STA Sep 03 '20 at 09:40
  • https://stackoverflow.com/a/32720098/7698734 – Hassaan Ali Sep 03 '20 at 09:41
  • @AlzafanChristian I need to write a query where I'm searching some data and need the date in a where clause, I'm using the day() method provide by mysql but I cant do it on a varchar.. – Romain Sep 03 '20 at 09:42
  • @droopsnoot do you know if there's a query that can do it automatically ? I've got like 5k row to change – Romain Sep 03 '20 at 09:45
  • @Romain, no sorry, I don't know enough sql to do that. It'd be pretty easy to do in PHP, I'm sure it's probably possible in a query too, especially if your dates and times are all the same length. – droopsnoot Sep 03 '20 at 09:53
  • @droopsnoot I've managed to change every '/' to '-' now I have to add some seconds – Romain Sep 03 '20 at 09:55

2 Answers2

0

Well, I've managed to change every '/' to '-'. Now my date format is something like '24-08-2020 22:05' I just need to add some second (something like 22:05:00) to each date to have a properly format for datetime

Romain
  • 3
  • 4
0

If you're using Laravel just use migrations... That'd be the cleanest way to do mass DB updates. Then update the records using their models, and use Carbon to handle the dates:

<?php

use App\TableModel;
use Carbon\Carbon;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class UpdateColumnDatatype extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // Get all the data to reformat it
        $data = TableModel::all();

        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn('column_name');          // Removes the old column

            // You need to set a ->default() or make this column ->nullable() or you'll
            // get errors since the data is empty now.
            $table->dateTime('column_name');            // Creates the new datetime
        });

        // Iterate over the old data to update the new rows
        foreach($data as $model) {
            $oldDate = Carbon::createFromFormat('d/m/Y h:i', $model->column_name);
            $newDate = $oldDate->format('Y-m-d H:i:s'); // Format for DateTime

            $obj = TableModel::find($model->id);
            $obj->column_name = $newDate;               // Change the record's data
            $obj->save();                               // Save the changes
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn('column_name'); // Removes the new column
            $table->string('column_name');     // Creates the old column as a string (varchar equivalent) 
            // Do the same thing to turn the new datetime values back to your old format
            // in case you decide to do a rollback
        });
    }
}
Zinapse
  • 28
  • 7