display records for today's date

Hi,

I'm having User model & it has created_at field.

If i want to find all the users who are created today. How can i write the query?

created_at field has datetime in it. how can i search for records in current date?

Thanks, Srikanth

Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, Date.tomorrow])

Michael Pavling wrote:

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

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)").

wow thanks for a brief explanation.. I will start using between..

You can also use following condition for today's date records

:conditions => ['created_at between ? and ?', date.beginning_of_day.to_s(:db), date.end_of_day.to_s(:db)]

Thanks Brijesh Shah

Apologies if this should be separated into a separate thread but this reminded me of a question I had when writing some search methods recently.

What, if any, are the differences between the following two find statements?

Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, Date.tomorrow])

-and-

Model.all(:conditions => {:created_at => Date.today..Date.tomorrow})

Is the choice just a personal coding preference or is there some performance or security differences between the two? It's my understanding that the array form's design was to help prevent sql injection attacks but I was unsure if you lost that benefit by using the hash form with a range.

Doesn't appear to be a difference in the SQL generated between those two statements.

Tested on Rails 3 rc2

As a matter of interest does that query include records where created at is exactly Date.tomorrow at time 00:00:00? If it does then it is not actually what the OP wanted I think.

Colin

Bob wrote:

Model.all(:conditions => {:created_at => Date.today..Date.tomorrow})

SELECT * FROM "meetings" WHERE ("meetings"."created_at" BETWEEN '2010-08-26' AND '2010-08-27')

Shouldn't this actually be:

Model.all(:conditions => {:created_at => Date.today...Date.tomorrow})

SELECT * FROM "meetings" WHERE ("meetings"."created_at" >= '2010-08-26' AND "meetings"."created_at" < '2010-08-27')