2

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());
}
2
  • i am confuse here a little. it sayd query builder in title but you tagged with eloquent. so which approach you are using? Commented Oct 9, 2013 at 15:18
  • @itachi: Using an Eloquent model based on the table and doing a direct query, i.e., DB::table('table') both exhibit the behavior I mention in my question. Commented Oct 9, 2013 at 15:35

2 Answers 2

9

You should add your column in the getDates() array. By doing this Eloquent will automatically convert your datetime to Carbon object.

From the docs:

By default, Eloquent will convert the created_at, updated_at, and deleted_at columns to instances of Carbon, which provides an assortment of helpful methods, and extends the native PHP DateTime class.

See this: http://laravel.com/docs/eloquent#date-mutators

Again, providing the date in the right format (Y-m-d) may still be necessary because it is not always possible to tell how to interpret a given date

Sign up to request clarification or add additional context in comments.

1 Comment

It seems like providing the right format is my only option, but I don't see why there would be any issues interpreting a column with the MySQL date type. Neither Eloquent nor the query builder seem able to compare the date type against either a \DateTime or a \Carbon\Carbon.
1

As told here, Laravel uses Carbon as a DateTime class, you can perform it using:

Model::whereDate( \Carbon\Carbon::now()->subDays(2) )->get();

12 Comments

That still doesn't work, but Model::whereDate(\Carbon\Carbon::now()->subDays(2)->toDateString())->get(); does.
It worked for me here when I posted. Could you, please, test it using QueryBuilder? >> DB::table('table')->whereDate( $date )->get(); Might be a bug on Eloquent.
It's the same in QueryBuilder. I should mention that the site is using PHP 5.3.3 (the latest version available in CentOS's package repository), so it's most likely a compatibility issue.
Where is located that whereDate method in the sources?
@RubensMariuzzo the whereDate is dynamically scoped. You can do this in Laravel: whereCOLUMN($value) which is basically a shorthand of where('COLUMN', '=', $value)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.