Level 75
Get results and just format it for display. https://www.php.net/manual/en/function.number-format.php
2 likes
Feb 12, 2022
12
Level 4
formatting date from a a query as 25 Mar, 2022
How do i correctly format date fromat query?
format 25 Mar, 2022
$group = DB::table('appointments')
->Where('doctor_id', $user)
->select(DB::raw('DATE(date) as date'), DB::raw('count(date) as count'))
->groupBy('date')->format('%d-%m-%Y')
->get();
Level 4
@jlrdw Thanks. I'm using Vue js. so i'll give it a go there!
Level 73
Try this
$group = DB::table('appointments')
->where('doctor_id', $user)
->select(DB::raw('DATE(date) as date'), DB::raw('count(date) as count'))
->groupBy('date')
->get()
->map(function ($row) {
$row['date'] = Carbon::parse($row->date)->format('d M, y');
return $row;
});
2 likes
Level 4
@Nakov
I get "message": "Cannot use object of type stdClass as array",
Level 27
@tomasosho $row['date'] should be $row->date in the closure, that’s all.
1 like
Level 75
$group = DB::table('appointments')
->selectRaw('DATE_FORMAT(date, "%d %b, %Y") as date, COUNT(date) as count')
->where('doctor_id', $user)
->groupBy('date')
->get();
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
2 likes
Level 4
@MichalOravec I get this error, i don't know if it's peculiar to laravel 8
My date is a column under the appointments table and it's getting the values.. all tested!
"message": "SQLSTATE[42703]: Undefined column: 7 ERROR: column \"%d %b, %Y\" does not exist\nLINE 1: select DATE_FORMAT(date, \"%d %b, %Y\") as date, COUNT(date) a...\n ^ (SQL: select DATE_FORMAT(date, \"%d %b, %Y\") as date, COUNT(date) as count from \"appointments\" where \"doctor_id\" = 1 group by \"date\")",
Level 75
@tomasosho I tested it and the provided query works with mysql in laravel 8.
1 like
Level 75
@tomasosho do you have date column?
Level 4
@MichalOravec
I'm using PostgreSQL not mysql. I guess the query doesn't work for PostgreSQL
Level 27
@tomasosho PostgreSQL doesn’t appear to have a DATE_FORMAT function like MySQL does. According to the first relevant Google hit, the PostgreSQL equivalent is to_char('[date]', '[format]).
1 like
Level 75
@tomasosho you could also get results with regular date and format for display in vue, via some js or moment js.
Please or to participate in this conversation.