Hi everybody, I've read lot of thread about this topic but still can't figure out the best practice to use.
I've this entry with a Date attribute, in the yyyy-mm-dd form.
I need to retrieve all the articles in a data Range, nothing of what I read really worked..what's the matter?
Using find(:all, :conditions .... ) I've tryed: - date between ? and ? - date => from..to - year(date)
nothing happened..is ruby able to evaluate range for Dates? please show me the way :S thx
Hi everybody, I've read lot of thread about this topic but still can't figure out the best practice to use.
I've this entry with a Date attribute, in the yyyy-mm-dd form.
is this attribute actually a date or a string containing a representation of a date ?
Fred
If and only if your date field in the database is a date or a time:
Model.all(:conditions => {:date => 7.days.ago…Date.today})
Model.all(:conditions => [“date BETWEEN ? AND ?”, 7.days.ago ,Date.today])
both generate and return valid results:
SELECT * FROM models WHERE (models.date BETWEEN ‘2009-09-25 12:35:21’ AND ‘2009-10-02’)
I prefer the first notation, but tastes might differ.
Best regards
Peter De Berdt
@Fred of course is a Date type and not string 
> I've this entry with a Date attribute, in the yyyy-mm-dd form.
> I need to retrieve all the articles in a data Range, nothing of what I > read really worked..what's the matter?
> Using find(:all, :conditions .... ) > I've tryed: > - date between ? and ? > - date => from..to > - year(date)
> nothing happened..is ruby able to evaluate range for Dates? > please show me the way :S
If and only if your date field in the database is a date or a time:
Model.all(:conditions => {:date => 7.days.ago..Date.today}) Model.all(:conditions => ["date BETWEEN ? AND ?",
7.days.ago ,Date.today])
good to know..and thanks for this..but what if a have to choose among latter periods like from 1993 to 2001? (I'm dealing with newspaper articles) And I have in the search form a select with all the available Dates (in the yyyy-mm-dd) format.
It could be good to me to only use year, so the question is how the get only the year from a Date type? I've tryed year(date) but it does not work (al least in the range actually)..or second aswer do I have to write a method that calculates how many days ago I want? even for 1993 ?
thanks so far
Andrea
Take the date from your form, which I think you are saying is a string in "yyyy-mm-dd" format and convert it into a Date object. Then you can use it in the find conditions as suggested above.
Colin
Anyway..this is the only that returns valid results! thanks man!
it works even with 7.years.ago ! cool 
so I'd need only to extract the year from the date params I get from the form..and then maybe calculete how many years ago it is. year(params[:date]) #?should this work?
> @Fred of course is a Date type and not string
>> > I've this entry with a Date attribute, in the yyyy-mm-dd form.
>> > I need to retrieve all the articles in a data Range, nothing of what I >> > read really worked..what's the matter?
>> > Using find(:all, :conditions .... ) >> > I've tryed: >> > - date between ? and ? >> > - date => from..to >> > - year(date)
>> > nothing happened..is ruby able to evaluate range for Dates? >> > please show me the way :S
>> If and only if your date field in the database is a date or a time:
>> Model.all(:conditions => {:date => 7.days.ago..Date.today}) >> Model.all(:conditions => ["date BETWEEN ? AND ?", >> 7.days.ago ,Date.today])
> good to know..and thanks for this..but what if a have to choose among > latter periods like from 1993 to 2001? > (I'm dealing with newspaper articles) > And I have in the search form a select with all the available Dates > (in the yyyy-mm-dd) format.
> It could be good to me to only use year, so the question is how the > get only the year from a Date type? I've tryed year(date) but it does > not work (al least in the range actually)..or second aswer do I have > to write a method that calculates how many days ago I want? even for > 1993 ?
Take the date from your form, which I think you are saying is a string in "yyyy-mm-dd" format and convert it into a Date object. Then you can use it in the find conditions as suggested above.
it's already a Date it's not a string as I wrote above
or maybe I'm wrong about the type... here's the form code in the view:
<% @source = Article.find(:all) %> From: <%= collection_select("from", "date", @source, "date","date", {:include_blank => true}) %> To: <%= collection_select("to", "date", @source, "date","date", {:include_blank => true}) %>
Model.all(:conditions => [“YEAR(date) BETWEEN ? AND ?”, 2000 ,2010]) works perfectly on mysql.
If your database doesn’t support it, Model.all(:conditions => [“YEAR(date) >= ? AND YEAR(date) <= ?”, 2000 ,2010]) might also work.
Depends on whether you want an exact range from a certain date to a certain date or a range of years which option to choose.
Best regards
Peter De Berdt
If you are using params[:from] then it is a string. params are always strings. The fact that you keep saying it is in yyyy-mm-dd format does not make sense if it is a Date as a Date is not in a particular format till you convert it to string for display for example.
Build a Date from the string and then you can compare it with your db value.
Colin
Thanks again to Peter..that's what I missed.. i need to call YEAR (date) even on the attribute side..not only for the variable.. :S thanks! I'll try
> or maybe I'm wrong about the type... > here's the form code in the view:
> <% @source = Article.find(:all) %> > From: > <%= collection_select("from", "date", @source, "date","date", > {:include_blank => true}) %> > To: > <%= collection_select("to", "date", @source, "date","date", > {:include_blank => true}) %>
If you are using params[:from] then it is a string. params are always strings. The fact that you keep saying it is in yyyy-mm-dd format does not make sense if it is a Date as a Date is not in a particular format till you convert it to string for display for example.
that's what I've realized few minutes ago 
so params[:from].to_date would do the job?!
Thanks again to Peter..that's what I missed.. i need to call YEAR (date) even on the attribute side..not only for the variable.. :S thanks! I'll try
> > or maybe I'm wrong about the type... > > here's the form code in the view:
> > <% @source = Article.find(:all) %> > > From: > > <%= collection_select("from", "date", @source, "date","date", > > {:include_blank => true}) %> > > To: > > <%= collection_select("to", "date", @source, "date","date", > > {:include_blank => true}) %>
> If you are using params[:from] then it is a string. params are > always strings. The fact that you keep saying it is in yyyy-mm-dd > format does not make sense if it is a Date as a Date is not in a > particular format till you convert it to string for display for > example.
that's what I've realized few minutes ago
so params[:from].to_date would do the job?!
or maybe
from = Date.new(params[:from])
?!
Check the docs for Date. You might have to use strptime or one of the other constructor methods. Make sure you catch exceptions if converting from user typed input.
Colin
ok..I swear this is the last (for today )
aut = params[:article][:author] from = params[:from][:date] to = params[:from][:date] tt = Date.strptime(to, "%Y-%m-%d") tf = Date.strptime(from, "%Y-%m-%d") @articles = Article.find(:all, :order => :date, :conditions => {:author => aut, :date.year => tf.year..tt.year})
everything but :date.year works ..I've already tried year(date)
I can't use between ? and ? cause I need to filter authors too.
so what?
(my eye is flickring :D)
thx guys
@articles = Article.find(:all, :order => :date, :conditions => [“(YEAR(date) BETWEEN ? AND ?) AND author_id=?”,tf.year, tt.year, aut.id])
Just out of the top of my head.
Best regards
Peter De Berdt
Occam is always right... the easiest solution if often the best one 
(parenthesys work)
morover.. when I use Article.scope(:all, .... it still behave like an array.. Instead using Article.scoped_by it seems to work better.. but I guess I can't do something like this:
proxy = Article.scoped_by_author_and_date(aut,"YEAR(date) BETWEEN ? AND ?",tf.year,tt,year) @articles = proxy.find_by_solr(params[:query]).results
in the server log I get this: Article Load (1.2ms) SELECT * FROM `articles` WHERE (articles.id in (1)) AND (`articles`.`date` = 'date BETWEEN ? AND ?' AND `articles`.`author` = 'No Author')
the question marks comes out :S
I'm hopeless
You didn’t put the conditions part into square brackets, that could be the reason.
Best regards
Peter De Berdt