On Aug 19, 2010, at 11:43 PM, Srikanth Jeeva wrote:
> Michael Pavling wrote:
>> On 20 August 2010 07:17, Srikanth Jeeva <[email protected]> wrote:
>>> created_at field has datetime in it. how can i search for records in
>>> current date?
>>
>> Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today,
>> Date.tomorrow])
>
> Thanks for the reply,,
>
> I used this ,
>
> Model.find(:all, :conditions=>["DATE(created_at) = ?", Date.today])
This will be slower than the BETWEEN option above. The issue with this is that
the database has to compute the date for every row in the table. Not an issue
if you don't have many rows. If you have lot of rows, it can be a noticeable
performance hit.
The other reason to use the BETWEEN option is if you have an index on
created_at. If you do, the BETWEEN option will use it. The DATE(created_at)
won't (unless you're index is specifically on "date(created_at)").
The below is from one of my PostgreSQL databases. press_releases.release_at
has an index on it. Notice that for the first query the database opts for a
"seq scan" (ie. search the entire database row by row, then filter it). The
second query uses the index. I don't have enough press releases for it to
matter, but if I did, the second would be much much faster.
development=# explain select * from press_releases where DATE(release_at) =
'2010-08-01';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on press_releases (cost=0.00..31.63 rows=1 width=839)
Filter: (date(release_at) = '2010-08-01'::date)
(2 rows)
development=# explain select * from press_releases where release_at BETWEEN
'2010-08-01' AND '2010-08-02';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_press_releases_on_release_at on press_releases
(cost=0.00..8.27 rows=1 width=839)
Index Cond: ((release_at >= '2010-08-01 00:00:00'::timestamp without time
zone) AND (release_at <= '2010-08-02 00:00:00'::timestamp without time zone))
(2 rows)
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
