Manning Date yyyy-mm-dd format

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

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

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

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?

:slight_smile:

> @Fred of course is a Date type and not string :wink:

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

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

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

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 :stuck_out_tongue: )

  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 :smiley:
(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 :smiley:

You didn’t put the conditions part into square brackets, that could be the reason.

Best regards

Peter De Berdt