How to use INTERVAL in CakePHP 3.x

Question

I am using CakePHP 3.x. I have converted and stored all my dates to string using the strtotime(date('Y/m/d h:i', time())); .And in DB it will store like this 1479466560 But Right now i need to select rows from exactly 14 days ago. I tried like below conditions,

$this->loadModel('Users');
$userList = $this->Users->find('all')
    ->select(['created'])
    ->where(['status' => 1,'created' => 'created + INTERVAL 14 DAY > NOW()']);

It is returning empty rows only. How to do this ?. Can any one help me !

Answer 1

I know this does not tell you how to use interval but it does solve the problem.

What I've done in the past is a simple >= < combination together with php variables:

$cur_date = date_create();
$date = clone $cur_date;
$date->modify("-12 hours");

$this->Model->find()
    ->where(['created >= ' => $date])
    ->andWhere(['created < '=> $cur_date]);

This effectively uses an interval. It is also nicely dynamically editable.

Answer 2

Your query doesn't match with any created date from your users table.

Your CakePHP3 query generated below SQL:

SELECT created FROM users WHERE (status = 1 AND created = created + INTERVAL 14 DAY > NOW())

Note: At First try and adjust above query from your phpmyadmin.

You can try below SQL for INTERVAL test

SELECT '2016-01-17 10:57:21' + INTERVAL 14 DAY > NOW();

OR

SELECT '2016-01-17 10:57:21' + INTERVAL 14 DAY;

Note: Adjust date string as you need. Now you can see what is the wrong you have done