Используйте date и strtotime
$today = 'WHERE date >= ' . date('Y-m-d 00:00:00', strtotime('today')) . ' AND date < ' . date('Y-m-d 00:00:00', strtotime('tomorrow'));
$yesterday = 'WHERE date >= ' . date('Y-m-d 00:00:00', strtotime('yesterday')) . ' AND date < ' . date('Y-m-d 00:00:00', strtotime('today'));
$this_week_sql = 'WHERE date >= ' . date('Y-m-d 00:00:00', strtotime('last monday')) . ' AND date < ' . date('Y-m-d 00:00:00', strtotime('next monday'));
И т.д.
Больше о форматах дат:
php.net/manual/ru/datetime.formats.php 
Средний
