Date find help please


#1

Here’s my problem.

I have an ‘Item’ model with a date column called “inserted_on” formatted
as
“yyyy-mm-dd hh:mm:ss:t”. I want to find all records that were inserted
more
than 30 days ago. How would I say that using the Item.find ( :all …)
method? I don’t fully understand the syntax. I tried

@result = Item.find( :all, :conditions => “inserted_on <
#{30.days.ago}”)
and got a MySQL syntax error.

Thanks in advance for the help.

Best Regards,
-Larry
“Work, work, work…there is no satisfactory alternative.”
— E.Taft Benson


#2

Larry K. wrote:

Here’s my problem.

I have an ‘Item’ model with a date column called “inserted_on” formatted
as
“yyyy-mm-dd hh:mm:ss:t”. I want to find all records that were inserted
more
than 30 days ago. How would I say that using the Item.find ( :all …)
method? I don’t fully understand the syntax. I tried

@result = Item.find( :all, :conditions => “inserted_on <
#{30.days.ago}”)
and got a MySQL syntax error.

Thanks in advance for the help.

Best Regards,
-Larry
“Work, work, work…there is no satisfactory alternative.”
— E.Taft Benson

Hello Larry

   I have an idea, there is a query in mysql to find the records 

based on the date (30 days ago)

SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)
<= date_col;

So by using “find_by_sql(str)” you will get the resultant.

just it is my view, I dont know whether it is usefull to you or not

Regards
------Surekha.Matte


#3

Larry,

The expression 30.days.ago results in an instance of Time, while in your
database you have some other format. So you need to find out how you can
format the Time thing into your database time format.

To illustrate, when you start scripts/console:

30.days.ago
=> Tue Apr 04 11:42:23 W. Europe Daylight Time 2006

Consider changing your time format to a full iso8601 format so you can
do:

30.days.ago.iso8601
=> “2006-04-04T11:44:05+02:00”

Regards,
Erik.

Larry K. wrote:

I have an ‘Item’ model with a date column called “inserted_on” formatted
as “yyyy-mm-dd hh:mm:ss:t”. I want to find all records that were inserted
more than 30 days ago. How would I say that using the Item.find ( :all
…)
method? I don’t fully understand the syntax. I tried

@result = Item.find( :all, :conditions => “inserted_on <
#{30.days.ago}”)
and got a MySQL syntax error.


#4

Larry,

A MySQL syntax error means that mysql doesn’t understand what you’re
telling it to do. Next time, look carefully at the error message (it
will include the query you just sent to the database) and ask
yourself “why would mysql get confused by that?”.

In your particular example: you haven’t quoted the date.

Regards,
Trevor


Trevor S.
http://somethinglearned.com