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
On 20 August 2010 07:17, Srikanth J. [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])
Michael P. wrote:
On 20 August 2010 07:17, Srikanth J. [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])
On Aug 19, 2010, at 11:43 PM, Srikanth J. wrote:
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
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 S.
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?
On Aug 20, 2:33 am, Michael P. [email protected] wrote:
On 20 August 2010 07:17, Srikanth J. [email protected] wrote:
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.
On Aug 25, 3:35 pm, Bob [email protected] wrote:
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
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’)
On 25 August 2010 21:04, Tim S. [email protected] wrote:
On Aug 20, 2:33 am, Michael P. [email protected] wrote:
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