Forum: Ruby comparing date problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
2c49cd424ddb3b3c9cef6ef7f45c8534?d=identicon&s=25 Sam Ginko (ginkod)
on 2009-01-19 16:18
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.
753dcb78b3a3651127665da4bed3c782?d=identicon&s=25 Brian Candler (candlerb)
on 2009-01-19 16:24
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/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, ...).
2c49cd424ddb3b3c9cef6ef7f45c8534?d=identicon&s=25 Sam Ginko (ginkod)
on 2009-01-19 16:38
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/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}
753dcb78b3a3651127665da4bed3c782?d=identicon&s=25 Brian Candler (candlerb)
on 2009-01-19 22:12
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.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...
which may be helpful for when you get to the free-text searching.
D2095ed7318c6969d30bcb6d898e5226?d=identicon&s=25 Taneal Bhandari (hadoob024)
on 2009-04-21 22:31
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)"
D2095ed7318c6969d30bcb6d898e5226?d=identicon&s=25 Taneal Bhandari (hadoob024)
on 2009-04-21 22:43
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?
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2009-04-22 01:21
(Received via mailing list)
On Apr 21, 2009, at 4:43 PM, Taneal Bhandari wrote:

> 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
Rob@AgileConsultingLLC.com
D2095ed7318c6969d30bcb6d898e5226?d=identicon&s=25 Taneal Bhandari (hadoob024)
on 2009-04-22 02:22
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://agileconsultingllc.com
> 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!
D2095ed7318c6969d30bcb6d898e5226?d=identicon&s=25 Taneal Bhandari (hadoob024)
on 2009-04-22 16:48
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://agileconsultingllc.com
> Rob@AgileConsultingLLC.com


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

-tan
This topic is locked and can not be replied to.