comparing date problem

[Sam Ginko]

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.

 

[Brian Candler]

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/examples.html (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, ...).

 

[Sam Ginko]

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/examples.html (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}

 

[Brian Candler]

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.findall, :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.findfirst, :conditions => "price = SELECT MAX(price) FROM shop")

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

Shop.findfirst, rder => "price desc")

ActiveRecord docs are at http://ar.rubyonrails.com/ 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-on-rails/
which may be helpful for when you get to the free-text searching.

 

[Taneal Bhandari]

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.findall, :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)"

 

[Taneal Bhandari]

now = Time.now
@events = Event.findall, :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?

 

[Rob Biedenharn]

now = Time.now
@events = Event.findall, :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://agileconsultingllc.com
(e-mail address removed)

 

[Taneal Bhandari]

: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://agileconsultingllc.com
(e-mail address removed)

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!

 

[Taneal Bhandari]

:conditions => [ 'event_date > ? OR (event_date = ? AND event_time

?)',

now.to_date, now.to_date, now.strftime("%H:%M:%S") ]
-Rob

Rob Biedenharn http://agileconsultingllc.com
(e-mail address removed)

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

-tan