When writing a query against a table with a date column using the Laravel query builder, what data type should be used? The following should return 13 results but instead returns nothing:
$date = new \DateTime("-2 days");
Model::whereDate($date)->get();
Dumping the query shows that laravel is trying this:
array(3) {
'query' =>
string(62) "select * from `table` where `date` = ?"
'bindings' =>
array(1) {
[0] =>
class DateTime#310 (3) {
public $date =>
string(19) "2013-10-07 14:39:11"
public $timezone_type =>
int(3)
public $timezone =>
string(3) "UTC"
}
}
'time' =>
double(5.55)
}
Writing the query as Model::whereDate($date->format("Y-m-d"))->get() instead works but doesn't seem like the Laravel way to do it.
Is there a specific object type I should be passing into the query to filter by a date column?
EDIT: The model in this example looks like this:
class Model extends Eloquent {
protected $table = 'table';
public function getDates() {
return array('date');
}
}
The table it refers to looks like this:
CREATE TABLE table(
some_field VARCHAR(255),
date DATE
) ENGINE = 'InnoDB';
Laravel thinks the date column is a string: Model::lists('date') returns an array of strings, as does DB::table('table')->lists('date').
Changing $date = new \DateTime("-2 days"); to $date = \Carbon\Carbon::now()->subDays(2) doesn't allow me to query the date column directly with the Carbon object.
UPDATED: For whatever reason, the built-in MySQL date to \Carbon\Carbon isn't working for me, so the simplest solution was to write one as a query scope:
public function scopeDate($query, \Carbon\Carbon $date) {
return $query->whereDate($date->toDateString());
}
DB::table('table')both exhibit the behavior I mention in my question.