[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

comparing date problem

Sam Ginko

1/19/2009 3:17:00 PM

I have a set of dates in my database and would like to only get the
latest one or the one closest to the current date. I'm trying to come up
with a way of doing it but had no luck. If any one could help I would
appreciate it.
--
Posted via http://www.ruby-....
8 Answers

Brian Candler

1/19/2009 3:23:00 PM

0

Sam Ginko wrote:
> I have a set of dates in my database and would like to only get the
> latest one or the one closest to the current date. I'm trying to come up
> with a way of doing it but had no luck. If any one could help I would
> appreciate it.

SQL problem? Show the SQL you've written already. Consider using the
max() operator. There are some good examples at
http://dev.mysql.com/doc/refman/5.0/en/exa... (which may be
useful even if you're not using MySQL)

ORM problem? Solution will depend on which ORM you are using (e.g.
ActiveRecord, Datamapper, Sequel, Og, ...).
--
Posted via http://www.ruby-....

Sam Ginko

1/19/2009 3:37:00 PM

0

Brian Candler wrote:
> Sam Ginko wrote:
>> I have a set of dates in my database and would like to only get the
>> latest one or the one closest to the current date. I'm trying to come up
>> with a way of doing it but had no luck. If any one could help I would
>> appreciate it.
>
> SQL problem? Show the SQL you've written already. Consider using the
> max() operator. There are some good examples at
> http://dev.mysql.com/doc/refman/5.0/en/exa... (which may be
> useful even if you're not using MySQL)
>
> ORM problem? Solution will depend on which ORM you are using (e.g.
> ActiveRecord, Datamapper, Sequel, Og, ...).


I'm using thinking sphinx search engine. an sql syntax in thinking
sphinx looks like this

@allRooms = Room.search :conditions => {:created_at => here is where I
should have a statement}
--
Posted via http://www.ruby-....

Brian Candler

1/19/2009 9:11:00 PM

0

Sam Ginko wrote:
> I'm using thinking sphinx search engine.

Sphinx is a new one on me - but looking at the project page, it looks
like it needs either a MySQL or PostgreSQL database sitting under it.

> an sql syntax in thinking
> sphinx looks like this
>
> @allRooms = Room.search :conditions => {:created_at => here is where I
> should have a statement}

I think you mean Room.find here, if this is ActiveRecord, in order to
do a SQL "select" on a table in the underlying database. If I'm right,
this is nothing to do with Sphinx's free-text searching. So it would be
something like

Room.find(:all, :conditions => { ... })

The SQL you need is at the link I gave before: see "The Row Holding the
Maximum of a Certain Column". All you need to do is to look at your
ActiveRecord docs to formulate a suitable query. Untested:

--SELECT article, dealer, price
--FROM shop
--WHERE price=(SELECT MAX(price) FROM shop);

Shop.find(:first, :conditions => "price = SELECT MAX(price) FROM shop")

--SELECT article, dealer, price
--FROM shop
--ORDER BY price DESC
--LIMIT 1;

Shop.find(:first, :order => "price desc")

ActiveRecord docs are at http://ar.rubyon... and there are also
some very good Rails books you can buy with good ActiveRecord coverage.

For further help on ActiveRecord you'd probably get best results going
to a Rails-specific mailing list.

Regards,

Brian.

P.S. I came across a blog post on Sphinx at
http://kpumuk.info/ror-plugins/using-sphinx-search-engine-in-ruby...
which may be helpful for when you get to the free-text searching.
--
Posted via http://www.ruby-....

Taneal Bhandari

4/21/2009 8:32:00 PM

0

I'm having a similar issue, but can't seem to figure out what the
solution is. Here's my code:

now = Time.now
@events = Event.find(:all, :conditions => ["event_date > " + now.to_s,
{:venue_id => @venue.id}])


Basically, I want to pull all the records from the Events table where
the event_date has not passed. The code above is generating the
following error:

"Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'Apr 21 16:31:05 -0400 2009)' at line 1: SELECT * FROM `events`
WHERE (event_date > Tue Apr 21 16:31:05 -0400 2009)"

--
Posted via http://www.ruby-....

Taneal Bhandari

4/21/2009 8:43:00 PM

0

Taneal Bhandari wrote:
> now = Time.now
> @events = Event.find(:all, :conditions => ["event_date > " + now.to_s,
> {:venue_id => @venue.id}])



Actually, I just realized that I also have to take into consideration
"event_time". Our table has 2 fields event_date and event_time, and I
only want to pull back records of events where both the event_date and
event_time have not passed. I've been playing around with it for a bit,
but it doesn't like any combination that I've tried. Any thoughts?
--
Posted via http://www.ruby-....

Rob Biedenharn

4/21/2009 11:17:00 PM

0

On Apr 21, 2009, at 4:43 PM, Taneal Bhandari wrote:

> Taneal Bhandari wrote:
>> now = Time.now
>> @events = Event.find(:all, :conditions => ["event_date > " +
>> now.to_s,
>> {:venue_id => @venue.id}])
>
> Actually, I just realized that I also have to take into consideration
> "event_time". Our table has 2 fields event_date and event_time, and I
> only want to pull back records of events where both the event_date and
> event_time have not passed. I've been playing around with it for a
> bit,
> but it doesn't like any combination that I've tried. Any thoughts?
> --


:conditions => [ 'event_date > ? OR (event_date = ? AND event_time
> ?)',
now.to_date, now.to_date, now.strftime("%H:%M:%S") ]

depending on what the format of event_time actually is, of course.

-Rob

Rob Biedenharn http://agileconsult...
Rob@AgileConsultingLLC.com

Taneal Bhandari

4/22/2009 12:22:00 AM

0

Rob Biedenharn wrote:
> :conditions => [ 'event_date > ? OR (event_date = ? AND event_time
> > ?)',
> now.to_date, now.to_date, now.strftime("%H:%M:%S") ]
>
> depending on what the format of event_time actually is, of course.
>
> -Rob
>
> Rob Biedenharn http://agileconsult...
> Rob@AgileConsultingLLC.com


Cool! Thanks! I'm actually home now so will give it a go in the
morning. But it looks like it should work. I believe that event_time
is the same format as what you have in your code. Thanks again! Will
update post in morning!
--
Posted via http://www.ruby-....

Taneal Bhandari

4/22/2009 2:48:00 PM

0

Rob Biedenharn wrote:
> :conditions => [ 'event_date > ? OR (event_date = ? AND event_time
> > ?)',
> now.to_date, now.to_date, now.strftime("%H:%M:%S") ]
> -Rob
>
> Rob Biedenharn http://agileconsult...
> Rob@AgileConsultingLLC.com


Just tested this out and it worked like a charm!!! Much thanks!

-tan
--
Posted via http://www.ruby-....