Manning Date yyyy-mm-dd format

Rails
1

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

2

On Oct 2, 12:12 pm, adedip [email protected] wrote:

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

3

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

On 2 Ott, 14:39, Peter De Berdt [email protected] wrote:

  • 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

4

On 02 Oct 2009, at 13:12, adedip wrote:

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])

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

5

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:

6

2009/10/2 adedip [email protected]:

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.

Colin

7

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}) %>

8

On 2 Ott, 17:35, Colin L. [email protected] wrote:

latter periods like from 1993 to 2001?
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:

9

2009/10/2 adedip [email protected]:

{: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.

Build a Date from the string and then you can compare it with your db
value.

Colin

10

On 02 Oct 2009, at 17:23, adedip wrote:

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 ?

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

11

On 2 Ott, 18:20, adedip [email protected] wrote:

here’s the form code in the view:
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])

?!

12

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

On 2 Ott, 18:04, Colin L. [email protected] wrote:

{: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?!

13

2009/10/2 adedip [email protected]:

<%= collection_select(“to”, “date”, @source, “date”,“date”,
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

14

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

15

On 02 Oct 2009, at 19:03, adedip wrote:

@articles = Article.find(:all, :order => :date, :conditions =>
{:author => aut, :date.year => tf.year…tt.year})

@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

16

On 3 Ott, 10:50, Peter De Berdt [email protected] wrote:

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:

17

On 03 Oct 2009, at 14:39, adedip wrote:

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

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

Best regards

Peter De Berdt