Fill new column with data modified manipulated from data in pre-existing data in another row in Laravel

Question

I have a pre-existing application built with a huge database. In this database, there's a column expires_in that holds a date but the date is saved in this format:

02 Mar 2018 ( i.e. 'd M Y' format)

I want to copy this data into a new column where it will be saved as date in expiration_date column (like this: 2018-03-02 ).

I want to do this in the most efficient way because the database has thousands of records.

Thank you.

This should be made in SQL, not in php. – Luis felipe De jesus Munoz Apr 18 '18 at 15:10 — Luis felipe De jesus Munoz, Apr 18 '18 at 15:10

score 2 · Answer 1 · answered Apr 18 '18 at 19:31

Use QueryBuilder's chunk method -- this fetches small subsets of data in a loop. Otherwise with thousands of records, your request could timeout or run out of memory.

You can then use Carbons createFromFormat() method to specify how to parse the current date format and update the record.

Accounts::orderBy('id')->chunk(100, function ($accounts) {
    foreach ($accounts as $account) {
        $account->update([
            'expiration_date' => Carbon::createFromFormat('d M Y', $account->expires_in)
        ]);
    }
});

score 0 · Accepted Answer · answered Apr 18 '18 at 15:29

0

Found this post about converting string to date

  Update `accounts` set `expiration_date` = STR_TO_DATE(expires_in, '%d %M %Y')

answered Apr 18 '18 at 15:29

Birendra Gurung

2,168
2
16
29

Fill new column with data modified manipulated from data in pre-existing data in another row in Laravel

2 Answers2