Laravel Eloquent compare date from datetime field

Question

I want to get all the rows from a table through an expression   table date  lt   2014-07-10   But if the column contains a datetime let s say   2014-07-10 12 00 00   But if I do    where  date     lt      date    it won t get the row   I guess this is because  date   2014-07-10 which makes MySQL assume that it is 2014-07-10 00 00 00   In regular MySQL I would just do  where DATE date   lt    date   What would be the equivalent using Laravel s Eloquent

User · Accepted Answer

Laravel 4  offers you these methods  whereDay    whereMonth    whereYear     3946  and whereDate     6879    They do the SQL DATE   work for you  and manage the differences of SQLite   Your result can be achieved as so   - gt whereDate  date     lt      2014-07-10     For more examples  see first message of  3946 and this Laravel Daily article    Update  Though the above method is convenient  as noted by Arth it is inefficient on large datasets  because the DATE   SQL function has to be applied on each record  thus discarding the possible index   Here are some ways to make the comparison  but please read notes below    - gt where  date     lt      2014-07-10 23 59 59    - gt where  date     lt     2014-07-11        2014-07-11   dayAfter    new DateTime  2014-07-10   - gt modify   1 day  - gt format  Y-m-d     - gt where  date     lt     dayAfter    Notes    23 59 59 is okay  for now  because of the 1-second precision  but have a look at this article  23 59 59 is not the end of the day  No  really  Keep in mind the  zero date  case   0000-00-00 00 00 00    Though  these  zero dates  should be avoided  they are source of so many problems  Better make the field nullable if needed

User · Answer

Have you considered using   where  date     lt     2014-08-11     You should avoid using the DATE   function on indexed columns in MySQL  as this prevents the engine from using the index   UPDATE  As there seems to be some disagreement about the importance of DATE   and indexes  I have created a fiddle that demonstrates the difference  see POSSIBLE KEYS

User · Answer

You can get the all record of the date  2016-07-14  by using it   whereDate  date       2016-07-14     Or use the another code for dynamic date  whereDate  date   date

User · Answer

You can use this  whereDate  date         date    If you give whereDate then compare only date from datetime field

User · Answer

If you re still wondering how to solve it   I use    protected  dates     created at   updated at   aired     In my model and in my where i do   where  aired    gt    time     So just use the unix to compaire in where    In views on the otherhand you have to use the date object   Hope it helps someone

[php] Laravel Eloquent compare date from datetime field

Examples related to php

Examples related to mysql

Examples related to datetime

Examples related to laravel-4