We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Models sql datediff

Hi!

I want to retrieve all posts older than 1h using datediff but I get the following error:

Phalcon\Mvc\Model\Exception: Column 'HOUR' doesn't belong to any of the selected models...

My code:

$h = 1;

Posts::find(['DATEDIFF(HOUR, created_at, NOW()) > :h:',
'bind' => ['h' => $h]
]);


145.0k
edited Mar '16

What database you are using ? beacause DATEDIFF in sql and mysql looks diffrently



845

I'm using mysql



145.0k
Accepted
answer


145.0k

Also possibly you should use TIMEDIFF in your case i guess.



7.0k
edited Mar '16

I think use Carbon should be more universal, like:

   public function getResultsBefore(Carbon\Carbon $time)
    {
        $className = get_class($this);
        return $this->getModelsManager->createBuilder()
            ->from($className)
            ->where('created_at < :time:',['time'=>$time->toDateTimeString()])
            ->getQuery()->execute();
    }

Then you could use like:

$hour = 1;
$posts = (new Posts)->getResultsBefore(\Carbon\Carbon::now()->addHour(-$hour));


7.0k
edited Mar '16

Or you could use Carbon like this:

    $hour = 1;
    $time = \Carbon\Carbon::now()->addHour(-$hour)->toDateTimeString();
    $posts = Posts::find(['created_at > :time:','bind'=>['time'=>$time]]);


845

I ended up using timediff and extracted the hour difference:

$h = 1;

Posts::find(['HOUR(TIMEDIFF(created_at, NOW())) > :h:',
'bind' => ['h' => $h]
]);