hi I want to build a filter that gets records from database based on selected date.such as
hi I want to build a filter that gets records from database based on selected date.such as #### Product.find(:all, :conditions => ["created_at = ?",selected_date]) #### 1 If selected_date is a Date object while created_at is a DateTime object,is it work? 2 Do i need to set this?
It will work just fine. However, it won't do what you want... created_at is going to contain date/time down to the second (or more). If you pass in a date of "2010-07-15" it's going to append a time of "00:00:00" (most likely). So you're not going to get records created anytime during that day.
I'm guessing that's what you want. If so you need to modify that to check for any record created on that *date* using a range or something else.
Try:
Product.find(:all, :conditions => ["DATE(created_at) = ?", selected_date])
-Rob
Rob Biedenharn Rob@AgileConsultingLLC.com http://AgileConsultingLLC.com/ rab@GaslightSoftware.com http://GaslightSoftware.com/
Will that have to do a calculation on every record to determine DATE? If so would it be more efficient to specify a range for created_at instead (something like >= selected_date and < selected_date + 1.day)? Particularly if there were an index on the column.
Colin
But have in mind that this wont use the created_at index (if you have one)
I want to build a filter that gets records from database based on selected date.such as #### Product.find(:all, :conditions => ["created_at = ?",selected_date]) #### 1 If selected_date is a Date object while created_at is a DateTime object,is it work? 2 Do i need to set this?
It will work just fine. However, it won't do what you want... created_at is going to contain date/time down to the second (or more). If you pass in a date of "2010-07-15" it's going to append a time of "00:00:00" (most likely). So you're not going to get records created anytime during that day.
I'm guessing that's what you want. If so you need to modify that to check for any record created on that *date* using a range or something else.
#### ActiveRecord::Base.default_timezone = :utc #### My talbe has another date type field which i want it to be local timezone,so it seems i should not set ActiveRecord::Base.default_timezone = :utc,but my filter will not works well because of the default local timezone setting for the createdz_at attribute.How to resolve these problem?
Thanks! --
Try:
Product.find(:all, :conditions => ["DATE(created_at) = ?", selected_date])
Will that have to do a calculation on every record to determine DATE?
Yes. And as Christos said, you lose the use of the index.
If so would it be more efficient to specify a range for created_at instead (something like >= selected_date and < selected_date + 1.day)? Particularly if there were an index on the column.
d = Time.now Product.find(:all, :conditions => ["created_at BETWEEN ? AND ?", d.beginning_of_day, d.end_of_day])
would work...
-philip
Note: this makes an assumption about what DB you're using -- e.g.
Oracle has no such function and will throw an error 
FWIW,
Thank you! it make me confident.
