2

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

Joaquin Marcher
  • 336
  • 5
  • 14
user11352561
  • 2,277
  • 12
  • 51
  • 102
  • https://stackoverflow.com/questions/6238992/converting-string-to-date-and-datetime – M. Suleiman Jul 30 '19 at 13:59
  • Possible duplicate of [Converting string to Date and DateTime](https://stackoverflow.com/questions/6238992/converting-string-to-date-and-datetime) – Watercayman Jul 30 '19 at 14:01
  • Its not a duplicate, OP is asking how to get the field from Db directly converted into timestamp, not how do you convert a normal date string in PHP to timestamp, its different – Abhay Maurya Jul 30 '19 at 14:03

3 Answers3

2

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();
oreopot
  • 3,392
  • 2
  • 19
  • 28
0

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(...).

Joaquin Marcher
  • 336
  • 5
  • 14
-1

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();
Abhay Maurya
  • 11,819
  • 8
  • 46
  • 64