I'd like to get the difference between two dates on two tables. The uploads.published_at compared to the completed_guides.created_at
I keep getting an error which says:
SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'datediff' (SQL: select users.*,completed_guides.created_at as completed_at,uploads.published_at,datediff(HOUR, published_at, completed_at) from `users` inner join `uploads` on `users`.`id` = `uploads`.`user_id` inner join `completed_guides` on `uploads`.`id` = `completed_guides`.`upload_id` where `completed_guides`.`upload_id` = 2839 limit 10)
Here is my code. Any help would be appreciated.
$select = [
'users.*',
'completed_guides.created_at as completed_at',
'uploads.published_at',
'datediff(HOUR, published_at, completed_at) as date_diff'
];
return User::select(DB::raw(join(',', $select)))
->join('uploads', 'users.id', '=', 'uploads.user_id')
->join('completed_guides', 'uploads.id', '=', 'completed_guides.upload_id')
->where('completed_guides.upload_id', $this->id)
->take(10)
->get();
You can use Laravel Query Builder's whereRaw()
like this:
return User::select(DB::raw(join(',', $select)))
->join('uploads', 'users.id', '=', 'uploads.user_id')
->join('completed_guides', 'uploads.id', '=', 'completed_guides.upload_id')
->where('completed_guides.upload_id', $this->id)
->whereRaw('datediff(uploads.published_at, completed_guides.completed_at) as date_diff')
->take(10)
->get();
To set the format of date you can use SQL method - date_format(date, format)
like this:
->select(DB::raw("DATE_FORMAT(date_diff, '%b %d %Y %h:%i %p') as formatted_date_diff"));
See more about SQL Date Format
Hope this helps!
The one with units, is timestampdiff, not datediff.
timestampdiff(HOUR, published_at, completed_at) as date_diff