about active_record timestamps

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 :slight_smile:

FWIW,

Thank you! it make me confident.