CODEWITHSUNDEEP
laravel
user11352561
user11352561

Reputation: 2647

Laravel - How to convert VARCHAR to TIMESTAMP or DATETIME using RAW DB

I am using Laravel 5.8 to run a query, from a table given to me. I observed that one of the fields (created_at) is varchar instead of timestamp or datetime. Note that I can not alter the table in my case.

$winners = DB::table('winner')
    ->select('msisdn', 'created_at')               
    ->orderByRaw('created_at DESC')
    ->get();

I am using DB Raw.

From my query, please how do I convert, created_at (varchar) to timestamp or datetime.

Thanks

Upvotes: 2

Views: 2689

Answers (3)

Joaquín Marcher
Joaquín Marcher

Reputation: 336

You can use Carbon, which is already included as a dependency of Laravel: Carbon documentation

$winners->map(static function($winner){
    $winner['created_at'] = \Carbon\Carbon::parse($winner['created_at']);

    return $winner;
});

But please notice that Carbon::parse() may not be the best option here, passing a valid format is better Carbon::createFromFormat(...).

Upvotes: 0

Abhay Maurya
Abhay Maurya

Reputation: 12277

Best way to do so is to use accessor

And inside your Winner model apply the accessor as:

public function getCreatedAtAttribute($date)
{
    return \Carbon\Carbon::parse($date);
}

This way you dont need to loop through your results after fetching it. Also you might be fetching winners in more than one controller, this way the created_at will always come as timestamp so you dont need to worry about it and write some code every time after fetching winners.

I thought it was obvious but since I got a negative vote for it. Please note to use model after defining the accessor like so:

$winners = \App\Winner::select('msisdn', 'created_at')               
    ->orderByRaw('created_at DESC')
    ->get();

Upvotes: -1

oreopot
oreopot

Reputation: 3450

Using the CAST function of SQL as below


$winners = DB::table('winner')
    ->select( 'msisdn', DB::raw('CAST(created_at AS DATETIME)') )               
    ->orderByRaw('created_at DESC')
    ->get();

Upvotes: 2

Related Questions