cakephp3 mysql 今日の日付のデータを取得

cakephp2 の時はもっと簡単にできたのに。
というのことで、cakephp3 + mysql で 今日のデータを取得する裏技を教えます。

created が今日の10日前より古いデータを取得

$date = new \DateTime();

$time = [
    'end' => $date->modify('-10 days')->format('Y-m-d 23:59:59')
];


$res = $this->Kankeis->find()
    ->where([
        'created <' => $time['end']
    ])
    ->first();

created が今日の10日前より新しいデータを取得

$date = new \DateTime();

$time = [
    'end' => $date->modify('-10 days')->format('Y-m-d 23:59:59')
];


$res = $this->Kankeis->find()
    ->where([
        'created >' => $time['end']
    ])
    ->first();

created が X 日前から Y日までのデータを取得
cakephp3 コード

$date = new \DateTime();

$time = [
    'start' => $date->format('Y-m-d 00:00:00'),
    'end' => $date->format('Y-m-d 23:59:59')
];

$res = $this->Kankeis->find()
    ->where([
        'created BETWEEN :start AND :end'
    ])
    ->bind(':start', $time['start'], 'datetime')
    ->bind(':end',   $time['end'], 'datetime')
    ->first();

pr($res);

実行後のログ

SELECT 
  Kankeis.id AS `Kankeis__id`, 
  Kankeis.social_id AS `Kankeis__social_id`, 
  Kankeis.target_social_id AS `Kankeis__target_social_id`, 
  Kankeis.omoi AS `Kankeis__omoi`, 
  Kankeis.created AS `Kankeis__created` 
FROM 
  kankeis Kankeis 
WHERE 
  (
    social_id = 1234 
    AND created BETWEEN '2017-09-26 00:00:00' 
    AND '2017-09-26 23:59:59'
  ) 
LIMIT 
  1

おまけ

$time = [

            'end' => $date->modify('-30 minutes')->format('Y-m-d H:i:s')//30分前
//            'end' => $date->modify('-1 hours')->format('Y-m-d H:i:s')//１時間前
        ];

解説
- 日時情報は php で取得しておく
- 'created BETWEEN :start AND :end' と bind を駆使する

DateTimeの使い方は詳しく
https://qiita.com/re-24/items/c3ed814f2e1ee0f8e811

にまとめられています。
ぜひご参考になすってください。

以上です。