Unix time Format not working in Laravel where clause

Question

I have a query in Laravel query builder like this. And I want to get data from a particular year

$query = blogs::where(array('blogs.status' => '1','blog_categories.status' => '1','categories.status' => '1','blogs.published' => '1'));

if (isset($year) and $year != NULL)$query->where("FROM_UNIXTIME(blogs.pubdate, '%Y')", '=', $year); 
    $result= $query->leftJoin('blog_categories', 'blog_categories.blogid', '=', 'blogs.blogid')->leftJoin('categories', 'blog_categories.catid', '=', 'categories.catid')->orderBy('blogs.publisheddate', 'desc')->count();

I got an error 'FROM_UNIXTIME' command. Here my 'blogs.pubdate' is in timestamp format (1456313400).Any idea?

Thanks for helping.

chanafdo · Accepted Answer · 2016-03-01 11:34:05Z

1

You need to use raw expressions

$query = blogs::where([
    'blogs.status' => '1',
    'blog_categories.status' => '1',
    'categories.status' => '1',
    'blogs.published' => '1'
]);

if (isset($year) && $year != NULL) {
    $query->whereRaw("FROM_UNIXTIME(blogs.pubdate, '%Y') = {$year}"); 
}

$result= $query->leftJoin('blog_categories', 'blog_categories.blogid', '=', 'blogs.blogid')
    ->leftJoin('categories', 'blog_categories.catid', '=', 'categories.catid')
    ->orderBy('blogs.publisheddate', 'desc')
    ->count();

answered Mar 1, 2016 at 11:34

chanafdo

5,1443 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unix time Format not working in Laravel where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related